CODEWITHSUNDEEP
sql-servert-sqldynamic
Hukd
Hukd

Reputation: 35

TSQL Control Flow Logic

I am working on building a procedure that uses basic dynamic SQL. I want to use the result of the dynamic SQL (@query) in another part of said procedure. Below is a shorthand version of the code I am attempting to complete.

WITHOUT THE USE OF sp_executesql, how can I go about passing the result value of @query into the IF blocks?

DECLARE @table VARCHAR(MAX)
DECLARE @query VARCHAR(MAX)
DECLARE @map VARCHAR(MAX)

SET @table = 'SomeTable'

SET @query = '
;WITH Assignment AS 
(
SELECT 
''' + @table + ''' AS src
,Type
,RANK () OVER(ORDER BY COUNT(type) as rnk
FROM ' + @table + '
GROUP BY Type
)
SELECT Type
FROM Assignment
WHERE rnk = ''1'''

IF (@query = 'typeA')
BEGIN
/* preform an upsert dynamically */
END

IF (@query = 'typeB')
BEGIN
/* preform a delete dynamically */
END

IF (@query = 'typeC')
BEGIN
/* preform an alter dynamically */
END

Upvotes: 1

Views: 105

Answers (2)

Julien Vavasseur
Julien Vavasseur

Reputation: 3952

Why are you testing @query right after it has been set with some SQL?

You could do it with a temp table:

Create Table #temp(type...)
SET @query = '
;WITH Assignment AS 
(
SELECT 
''' + @table + ''' AS src
,Type
,RANK () OVER(ORDER BY COUNT(type) as rnk
FROM ' + @table + '
GROUP BY Type
)
Insert Into #temp(type)
SELECT Type
FROM Assignment
WHERE rnk = ''1'''

You can also build your dynamic query in your if statement although I am not sure it would work in your case:

SET @q1 = '
;WITH Assignment AS 
(
SELECT 
''' + @table + ''' AS src
,Type
,RANK () OVER(ORDER BY COUNT(type) as rnk
FROM ' + @table + '
GROUP BY Type
)'
set @q2 = 'SELECT Type
FROM Assignment
WHERE rnk = ''1'''

Case When @type = 'A' then @query = @q1 + 'Insert into... ' + @q2 
Case When @type = 'B' then @query = @q1 + 'Update... ' + @q2 
Case When @type = 'B' then @query = @q1 + 'delete from where type in (' + @q2 + ')' end

If you change you mind, it is also easy with sp_executesql:

create table #temp(type int)
insert into #temp 
exec sp_executesql @query

or if there are not thousands of rows:

declare @temp table(type int)
insert into @temp
exec sp_executesql @query

If there is only one row, still with sp_executesql and a parameter, this is the best option:

declare @type varchar(10)
SET @query = '
    declare @type varchar(10)    
;WITH Assignment AS 
(
SELECT 
''' + @table + ''' AS src
,Type
,RANK () OVER(ORDER BY COUNT(type) as rnk
FROM ' + @table + '
GROUP BY Type
)
SELECT @type = Type
FROM Assignment
WHERE rnk = ''1''';

exec sp_executesql @query, N'@type varchar(10)', @type = @type

Upvotes: 2

Paul Spain
Paul Spain

Reputation: 549

This is one way to get data out of dynamic SQL

DECLARE @SQL VARCHAR(MAX)

--Dynamic SQL
SET @SQL = '
--Do anything you like in here as long as you select the results in the @Data Table format at the end
SELECT 132'

--How to get the result out of the dynamic SQL (into a table)
DECLARE @Data TABLE (Value INT)
INSERT INTO @Data(Value)
EXEC(@SQL)

--Get the result out of the table into a local (if you need to)
DECLARE @MyValue INT

SELECT @MyValue  = Value FROM @Data

--Do what you like with the value now we are back in normal SQL
PRINT @MyValue

Upvotes: 0

Related Questions