0

I am using dynamic SQL.

I inserted a select statement into one row from #example, I want to call that select statement and run it with dynamic SQL, my select stored has a variable call @NumOrder

create table #example(id int, description varchar(1000))

insert into #example(id, description)
values(1, 'select case when name=''Carlos'' then ''Pass'' else ''FAIL'' from server.dbo.person where number = @NumOrder')

declare @NumOrder int, @SQL nvarchar(max)

set @NumOrder=2621
set @SQL='description' 
--here is where I want to call my query and is failing 
--once I filled @NumOrder I want to display the results
exec(@SQL)

I want to run this dynamic SQL using a row stored into the temp table, I know that I can put the complete select statement into the @SQL and works but I want to know if there is a way to call the select statement in a row stored from a table. Is there a way to do this?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Webb
  • 167
  • 4
  • 15
  • 3
    If you use [sp_executesql](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-ver15) (which is the recommended way to call SPs) you can pass parameters. – Dale K Feb 16 '22 at 05:02
  • Hi, but in these case I want to call a row from a temp table – Webb Feb 16 '22 at 13:21
  • I don't know what you mean by "I want to call a row from a temp table". With dynamic SQL you can construct *any* regular query, you are just building it as a string rather than statically. – Dale K Feb 16 '22 at 19:14
  • 1
    So if you want to apply a rule to multiple rows in a table you could do `set @Sql = 'select case when ' + @Rule + ' then 1 else 0 end RulePassed from dbo.MyTable where {Some Condition}';` or similar... but really need more detail on what you are trying to accomplish. – Dale K Feb 16 '22 at 19:21

0 Answers0