Using string variables to be part of tsql code

Question

On tsql, I want to use variable as part of tsql code according to some conditions, is there a way to do the following:

DECLARE @mode BINARY = 0
DECLARE @stringCode VARCHAR(100)
.
.
.
if(@mode = 1)(
    @stringCode VARCHAR(100) = 'charindex(",", rtrim(col1_name), col2_name)'
)ELSE(
    @stringCode VARCHAR(100) = '1=1'
)

SELECT col1_name, col2_name, col3_name
    FROM table1_name AS T1
    INNER JOIN table2_name AS T2
    ON @stringCode

The last query using the variable @stringCode as part of the code.

Well, it did not work like that, so I wanted to know if I could possibly apply this?

Please note that this code is just a test to demonstrate what I want to do

If you want to do this, then you need to learn about dynamic SQL. If you are attempting this and don't know what dynamic SQL is, you may need to learn about SQL in general. — Gordon Linoff
– Gordon Linoff, Commented Dec 8, 2016 at 15:26
Thanks for the note, I will read about dynamic SQL. I am glad that it is possible though — Ahmed Ali
– Ahmed Ali, Commented Dec 8, 2016 at 15:40

Андрей Гальченко · Accepted Answer · 2016-12-08 16:09:42Z

1

All you need is 'sp_executesql' system stored proc. It takes a string containing the query as input:

DECLARE @query nvarchar(4000)
SET @query = N'SELECT col1_name, col2_name, col3_name
    FROM table1_name AS T1
    INNER JOIN table2_name AS T2
    ON ' + @stringCode

EXEC sp_executesql @query

answered Dec 8, 2016 at 16:09

Андрей Гальченко

111 bronze badge

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

Comments

Juan Carlos Oropeza · Accepted Answer · 2016-12-08 15:32:28Z

0

I guess you could try something like this, but not sure if those are right join conditions.

if(@mode = 1)(
    SELECT col1_name, col2_name, col3_name
    FROM table1_name AS T1
    INNER JOIN table2_name AS T2
       ON charindex(",", rtrim(col1_name), col2_name)
)ELSE(
    SELECT col1_name, col2_name, col3_name
    FROM table1_name AS T1
    INNER JOIN table2_name AS T2
       ON 1=1
)

answered Dec 8, 2016 at 15:32

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

4 Comments

Ahmed Ali Over a year ago

Thanks Juan, this what I am using now, but the reason I need to use a variable because there are more conditions and I also want to apply the same concepts in different ways. Your suggestion is good but not using what I am looking for

Juan Carlos Oropeza Over a year ago

Then you need go for the dynamic SQL. as suggested in comments ;) , But again, you should double check those JOIN conditions, looks very weird

Ahmed Ali Over a year ago

Yes, sorry I wrote the conditions just to demonstrate the code and they do look weird :)

Ahmed Ali Over a year ago

Thanks @Juan Carlos Oropeza

Collectives™ on Stack Overflow

Using string variables to be part of tsql code

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related