CODEWITHSUNDEEP
t-sql
Madhu
Madhu

Reputation: 57

Pass one query value to another query in SQL Server

I need to pass one query values to another query as "table name" and "column name" like below

SELECT Value1 AS [MyTableName],Value2 AS [MyColName]
FROM MyTable

Pass the above result to below query as follows.

SELECT * FROM [MyTableName]
WHERE ColumnValue = MyColName

In this case MyTableName is Value1 from the above result and MyColName is Value2. Is it possible to combine the above result into a single query.

Upvotes: 0

Views: 1331

Answers (1)

George Menoutis
George Menoutis

Reputation: 7240

You need to use dynamic sql.

To protect against injection, we will use these: 1) sp_executesql, so that we can do 3) 2) quotename for the system objects (ie: table and column) names 3) pass the column value through a parameter

declare 
     @mytablename nvarchar(128)
    ,@mycolname nvarchar(128)
    ,@mycolvalue nvarchar(max) -- or whatever your column type is
    ,@stmt nvarchar(max)
    ,@params nvarchar(max)

select @mytablename=quotename(Value1),@mycolname=quotename(Value2) AS [MyColName]
FROM MyTable

set @mycolvalue = 'myColValueData' -- or whatever the value is
set @stmt=
N'select * from '+@mytablename
+nchar(13)+nchar(10)+'where '+@mycolname+' = @mycolvalue'
set @params='@mycolvalue nvarchar(max)'  -- or whatever your column type is

exec sp_executesql(@stmt,@params,@mycolvalue=@mycolvalue)

Upvotes: 1

Related Questions