Dynamic sort

[vanikanc]

Hello, I want to pass the sort as an input parameter to the stored proc. I am constructing a dynamic sql and executing it. The problem is more like a syntax issue, where in I an checking for certain value in sql. ---- Set @sql1 = ' SELECT distinct sd.storeno storeno, sd.storename storename FROM test_table sd where sd.status='N' ORDER by' set @sql1 = @sql1 + ' ' + @sort --------- How would I check for additional conditions in this sql? I tried to enclose N in double still did not work. Many thanks!

[Herman T Instance]

you should use '''N'''

In Word you can only store 2 bytes. That is why I use Writer.

[vanikanc]

Thank you for your response. It should be within two single quotes, like ''N''

[Corporal Agarn]

That would be ''''N'''' try:

declare @sql1 varchar(2000)
Set @sql1 = '
SELECT distinct
sd.storeno storeno,
sd.storename storename
FROM
test_table sd
where sd.status=''''N''''
ORDER by'

print @sql1

Output: SELECT distinct sd.storeno storeno, sd.storename storename FROM test_table sd where sd.status=''N'' ORDER by

[Lost User]

When you wish to embed single quotes within dynamic strings, use two consecutive single quotes, like this:

Set @sql1 = '
SELECT distinct
sd.storeno storeno,
sd.storename storename
FROM
test_table sd
where sd.status=''N''
ORDER by'

set @sql1 = @sql1 + ' ' + @sort

EDIT: Corrected a mistake.

modified on Monday, August 1, 2011 2:49 PM

[vanikanc]

I don't know but just the double single quotes worked for me. ''N''

[jschell]

Seems to be some confusion in the back and forth in the answers above. A SQL text literal is expressed like the following example.

'abc'

If you want to embed a single tick in the above literal you MUST escape it. The standard SQL (ANSI) idiom for that is as follows (but some databases offer other ways.)

'a''bc'

So two single ticks become one. Three ticks is NEVER correct.