SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 execute multi select..
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

veronika.np
Starting Member

Iran
29 Posts

Posted - 09/21/2012 :  13:35:36  Show Profile  Reply with Quote
hi friends,
i have a question..
i have a query and i want to execute query column but i don`t know how can i do it?
please help me..

declare @table_schema nvarchar(50) = 'dbo',
        @table_name nvarchar(50) = 'TableA',
        @id int = 1
(select fk_col.TABLE_SCHEMA, fk_col.TABLE_NAME, fk_col.COLUMN_NAME, 
    'select * from ' + fk_col.TABLE_SCHEMA + '.' + fk_col.TABLE_NAME + ' t1 '
        + ' inner join ' + @table_schema + '.' + @table_name + ' t2 '
        + ' on t1.' + fk_col.COLUMN_NAME + ' = t2.' + pk_col.COLUMN_NAME
        + ' where t2.' + pk_col.COLUMN_NAME + ' = ' + cast(@id as nvarchar)) query

from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk

    join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE pk_col
        on pk.CONSTRAINT_SCHEMA = pk_col.CONSTRAINT_SCHEMA
        and pk.CONSTRAINT_NAME = pk_col.CONSTRAINT_NAME

    join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS fk 
        on pk.CONSTRAINT_SCHEMA = fk.UNIQUE_CONSTRAINT_SCHEMA 
        and pk.CONSTRAINT_NAME = fk.UNIQUE_CONSTRAINT_NAME

    join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE fk_col
        on fk_col.CONSTRAINT_SCHEMA = fk.CONSTRAINT_SCHEMA
        and fk_col.CONSTRAINT_NAME = fk.CONSTRAINT_NAME

where pk.TABLE_SCHEMA = @table_schema 
    and pk.TABLE_NAME = @table_name
    and pk.CONSTRAINT_TYPE = 'PRIMARY KEY'

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/21/2012 :  13:42:53  Show Profile  Reply with Quote
use sp_executesql

http://msdn.microsoft.com/en-us/library/ms175170(v=sql.105).aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

veronika.np
Starting Member

Iran
29 Posts

Posted - 09/21/2012 :  13:47:37  Show Profile  Reply with Quote
thanks for reply but when i do it i see query in rows i want to execute them ?how can i do it?
please help me...
quote:
Originally posted by visakh16

use sp_executesql

http://msdn.microsoft.com/en-us/library/ms175170(v=sql.105).aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/21/2012 :  14:14:30  Show Profile  Reply with Quote
hmm..can you show the code used?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1754 Posts

Posted - 09/21/2012 :  19:16:13  Show Profile  Reply with Quote
If I understand what you are trying to accomplish (and I really might not), you are using the SELECT statement to generate some SQL code and you'd like to execute that code instead of merely returning it. If so, you'll want to insert the results of the SELECT into a (temp?) table and then iterate through the table using EXEC sp_executeSQL on each record's SQL statement. There is no way to have the SELECT statement automatically execute the SQL you are generating.

=================================================
We are far more concerned about the desecration of the flag than we are about the desecration of our land. -Wendell Berry
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000