Assign a sql script into a variable

Question

This is my first time doing this and the reason is because I would like to avoid typing the same sql script code when using the conditional "if". My first option was creating a variable and assign the "select" statement to it , now depending on another variable value , I will just change the "where" condition but instead of getting all the columns and rows that I get on a regular sql script , I'm getting just one cell with the text assigned to the variable. Below is my code:

DECLARE @SQLScript varchar(MAX)='' ,
        @Subordinate int = 1;

SELECT @SQLScript = N'SELECT * FROM Account
    WHERE AccountID = 345015' 
IF @Subordinate = 1 
BEGIN
    SELECT @SQLScript =@SQLScript +' AND Status=2 ORDER BY 1 asc' 
END 
ELSE 
BEGIN
    SELECT @SQLScript =@SQLScript +' AND Status=1 ORDER BY 2 asc'
END
SELECT @SQLScript

What I'm getting after executing the above script is 1 cell showing the following text "SELECT * Account where AccountID = 345015 AND Status=2 group by 1 asc" . What I need is to get the table data from that script

sp_executesql

McNets
– McNets

2018-11-30 15:09:36 +00:00
Commented Nov 30, 2018 at 15:09 — McNets
– McNets, Commented Nov 30, 2018 at 15:09
Just making sure; are yo using SQL Server?

Stuart Ainsworth
– Stuart Ainsworth

2018-11-30 15:28:10 +00:00
Commented Nov 30, 2018 at 15:28 — Stuart Ainsworth
– Stuart Ainsworth, Commented Nov 30, 2018 at 15:28
Yes I'm using Microsoft SQL Server Management Studio

Samayoa
– Samayoa

2018-11-30 15:32:02 +00:00
Commented Nov 30, 2018 at 15:32 — Samayoa
– Samayoa, Commented Nov 30, 2018 at 15:32

Zeki Gumus · Accepted Answer · 2018-11-30 15:46:29Z

1

You need yo execute your dynamic script with simply using EXEC(). Also you need to put FROM to your select, use ORDER BY instead of GROUP BY to order your query. Her is your corrected script :

With using EXEC() :

Declare 
@SQLScript varchar(max)=''
,@Subordinate int = 1;

Select @SQLScript = N'SELECT * FROM Account
where AccountID = 345015'

if @Subordinate = 1
begin
Select @SQLScript =@SQLScript +' AND Status=2 order by 1 asc'
end

else
begin
Select @SQLScript =@SQLScript +' AND Status=1 order by 2 asc'
end

--select @SQLScript
exec(@SQLScript)

With using sp_executesql. Be aware, you cannot use VARCHAR to execute your query with sp_executesql . You must use NVARCHAR(or ntext,nchar).

Declare 
@SQLScript NVARCHAR(max)=''
,@Subordinate int = 1;

Select @SQLScript = N'SELECT * FROM Account
where AccountID = 345015'

if @Subordinate = 1
begin
Select @SQLScript =@SQLScript +' AND Status=2 order by 1 asc'
end

else
begin
Select @SQLScript =@SQLScript +' AND Status=1 order by 2 asc'
end

--select @SQLScript
EXECUTE sp_executesql @SQLScript

edited Nov 30, 2018 at 15:46

answered Nov 30, 2018 at 15:21

Zeki Gumus

1,47410 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Stuart Ainsworth Over a year ago

+1 for correcting the script. Although, I would recommend that they use sp_executesql instead of just exec for the reasons listed here: blogs.msdn.microsoft.com/turgays/2013/09/17/…

Stuart Ainsworth Over a year ago

and this is all assuming they're using SQL Server :)

Zeki Gumus Over a year ago

@StuartAinsworth I will update the script for both solutions. Thanks for double checking.

George Joseph · Accepted Answer · 2018-11-30 15:20:27Z

1

Just use

execute sp_executesql @SQLScript

to execute the query

answered Nov 30, 2018 at 15:20

George Joseph

5,93213 silver badges25 bronze badges

Collectives™ on Stack Overflow

Assign a sql script into a variable

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related