store Value in variable return by execute statement

Question

0.00/5 (No votes)

See more:

SQL

declare @value varchar(500)
declare @Whrcol varchar(500)
declare @Whrvalue varchar(500)
set @Whrcol='Product Cat3'
set @Whrvalue='IR DOME CAMERA'
set @value=('select [Table Name] from tblProductsCatalog where ['+ @Whrcol+ '] ='''+@Whrvalue+'''')
execute (@value)

I want to save the value return by Execute statement so that i can use in next statement
i.e.
execute('select * from '+@value+ ' where IsActive =1')
getting error....
How can i do it

im trying from several hours but not able to solve 
please help

Posted 5-Mar-13 0:15am

sanchu90

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-03-05T00:22:00

Solution 1

To get value, use this:

SQL

declare @value varchar(500)
declare @Whrvalue varchar(500)
set @Whrvalue='IR DOME CAMERA'

SELECT @value=[Table Name]
FROM tblProductsCatalog
WHERE [Product Cat3]= @Whrvalue
SELECT @value AS [Table Name]

[EDIT]
OK, if you want to build dynamic queries and get value returned by this query, see this:
http://stackoverflow.com/questions/803211/how-to-get-sp-executesql-result-into-a-variable[^]

[/EDIT]

Posted 5-Mar-13 0:22am

Maciej Los

Updated 5-Mar-13 1:40am

v3

Comments

sanchu90 5-Mar-13 6:27am

HEy I Want to pass column name thrugh parameter

im not able to it with your Query

Maciej Los 5-Mar-13 7:12am

Sorry, but in this context, everything is wrong.
Please see these:
http://msdn.microsoft.com/en-us/library/ms187953%28v=sql.105%29.aspx
http://www.databasejournal.com/features/mssql/article.php/3087431/T-SQL-Programming-Part-1---Defining-Variables-and-IFELSE-logic.htm
http://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/
http://stackoverflow.com/questions/10781808/tsql-how-to-execute-query-as-variable

Davidduraisamy 5-Mar-13 6:33am

In select statement you have taken Table name.Take Column name.

declare @value varchar(500)
declare @Whrcol varchar(500)
declare @Whrvalue varchar(500)
set @Whrcol='Product Cat3'
set @Whrvalue='IR DOME CAMERA'
set @value=('select [Column Name] from tblProductsCatalog where ['+ @Whrcol+ '] ='''+@Whrvalue+'''')
execute (@value)

Maciej Los 5-Mar-13 7:40am

See my updated solution ;)

sanchu90 5-Mar-13 6:40am

[Table Name] is my column name for that table as that store tablenames of database

Maciej Los 6-Mar-13 8:44am

See my updated solution.

Maciej Los 6-Mar-13 8:45am

See my updated solution

gvprabu · Answer 2 · 2013-03-05T00:44:00

Hi

Try This....

SQL

declare @value varchar(500)
declare @Whrcol varchar(500)
declare @Whrvalue varchar(500)
declare @TableDtls TABLE(TableName VARCHAR(500))
set @Whrcol='Product Cat3'
set @Whrvalue='IR DOME CAMERA'
set @value=('select [Table Name] from tblProductsCatalog where ['+ @Whrcol+ '] ='''+@Whrvalue+'''')
INSERT INTO @TableDtls(TableName) 
execute (@value)
SELECT TableName FROM @TableDtls

Regards,
GVPrabu

Davidduraisamy · Answer 3 · 2013-03-05T00:36:00

SQL

In select statement you have taken Table name.Take Column name.

declare @value varchar(500)
declare @Whrcol varchar(500)
declare @Whrvalue varchar(500)
set @Whrcol='Product Cat3'
set @Whrvalue='IR DOME CAMERA'
set @value=('select [Column Name] from tblProductsCatalog where ['+ @Whrcol+ '] ='''+@Whrvalue+'''')
execute (@value)

Khorshed Alam, Dhaka · Answer 4 · 2013-03-05T16:38:00

Have a look.

SQL

DECLARE @value VARCHAR(500)
DECLARE @Whrcol VARCHAR(500)
DECLARE @Whrvalue VARCHAR(500)
SET @Whrcol = 'Product Cat3'
SET @Whrvalue = 'IR DOME CAMERA'
SET @value = 'SELECT [Column Name] INTO TestTable 
					FROM tblProductsCatalog 
              WHERE [' + @Whrcol +'] =''' + @Whrvalue + ''''
    
--PRINT @value
EXECUTE (@value)

SELECT * FROM TestTable

store Value in variable return by execute statement

4 solutions

Solution 1

Solution 3

Solution 2

Solution 4

Add your solution here

Preview 0