How to Make a Dynamic Stored Procedure Query

Question

How to Make a Dynamic Stored Procedure Query

Viewing 13 posts - 46 through 57 (of 57 total)

You must be logged in to reply to this topic. Login to reply

dso808 SSCrazy Points: 2102 More actions · Answer 1

Gail - you are awesome. Thanks again.

I did read and use your example on your blog as my template. Unfortunately, in the proces of making my changes, I started to get glassy-eyed, cross-eyed, blurred vision, whatever you wanna call it and I inexplicably put those '=' signs in...

Then it was a case of being unable to "...see the forest for the trees...". So thanks for the second set of eyes. That's all I needed. Looks good to go now.

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 2

Hi dso808,

Also you have to initialize @Where NVARCHAR(4000) to ' ' otherwise the

entire string will be NULL.

Thanks & Regards,
MC

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

only4mithunc (12/15/2010)
Also you have to initialize @Where NVARCHAR(4000) to ' ' otherwise the
entire string will be NULL.

No he doesn't.

He's initialising the string right after declaration. Initialising it with the base SQL statement.

DECLARE @SQL NVARCHAR(4000), @Where NVARCHAR(4000)

SET @SQL = 'SELECT * FROM ProblemTicket '

The time you need to initialise the string to '' is when every operation on that string concatenates something to it. In the following example, without initialising the string to '' it will be null at the end because all the assignments are concatenation..

DECLARE @SomeString varchar(4000)

SET @SomeString = @Somestring + 'Value1 '

SET @SomeString = @Somestring + 'Value2 '

SET @SomeString = @Somestring + 'Value3 '

SET @SomeString = @Somestring + 'Value4 '

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 4

Gila I do agree with you.

But he has declared @WHERE and assume the first parameter value is not null , the the statement is

SET @WHERE = @WHERE + 'something'

I think now the value of @WHERE is NULL and so the statement @SQL +@WHERE as well

DECLARE @sql NVARCHAR(100)

DECLARE @where NVARCHAR(30)

DECLARE @parameter INT = NULL

IF @parameter is not null

BEGIN

SET @sql = 'hello'

END

SET @where = @where + 'world'

SELECT @sql + @where

Please correct if I'm wrong

Thanks & Regards,
MC

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 5

Sorry...

In the above reply example I wanted to give like this

DECLARE @sql NVARCHAR(100)

DECLARE @where NVARCHAR(30)

DECLARE @parameter INT = NULL

SET @sql = 'hello'

IF @parameter is not null

BEGIN

SET @where = @where + 'world'

END

SELECT @sql + @where

Thanks & Regards,
MC

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

only4mithunc (12/15/2010)
Gila I do agree with you.
But he has declared @WHERE and assume the first parameter value is not null , the the statement is
SET @WHERE = @WHERE + 'something'

Ah, yes, the WHERE does need to be initialised, the SQL already is.

Quickest fix for that is to replace the line that declares the variables with this:

DECLARE @SQL NVARCHAR(4000), @Where NVARCHAR(4000) = ''

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dso808 SSCrazy Points: 2102 More actions · Answer 7

Hi Gail - to intialize @Where, I did this right after declaring it:

SET @Where = ''

When I tried to do this:

@Where NVARCHAR(4000) = ''

I got this error message:

Cannot assign a default value to a local variable.

BTW, I'm trying to use 'LIKE' now in my SP and am struggling to get the '%' to work with the statement. Example:

SET @Where = @Where + 'AND ProbTicketSubDept LIKE %@_Dept% '

The above returns an incorrect syntax error. Suggestions? Thanks.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 8

dso808 (12/15/2010)
Hi Gail - to intialize @Where, I did this right after declaring it:
SET @Where = ''
When I tried to do this:
@Where NVARCHAR(4000) = ''
I got this error message:
Cannot assign a default value to a local variable.

Gail gave you 2k8 syntax. It's easier, but for 2k5 you still need to do it in two lines:

DECLARE @var

SET @var = ''

BTW, I'm trying to use 'LIKE' now in my SP and am struggling to get the '%' to work with the statement. Example:
SET @Where = @Where + 'AND ProbTicketSubDept LIKE %@_Dept% '
The above returns an incorrect syntax error. Suggestions? Thanks.

First problem is the like target needs to be quoted. Needs to be: (notice that is two ' , not a single ")

SET @Where = @Where + 'AND ProbTicketSubDept LIKE ''%@_Dept%'' '

The '' (two apostrophe's) is an escape to leave a single ' in a string.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

dso808 SSCrazy Points: 2102 More actions · Answer 9

Hi Craig and thanks. OK, I added those single quotes like so:

SET @Where = @Where + 'AND ProbTicketSubDept LIKE ''%@_Dept%'' '

I got no errors but no records were returned either. I did a 'print' upon running the statement and it returned this:

SELECT * FROM ProblemTicket WHERE ProbTicketSubDept LIKE '%@_Dept%'

Does that provide any clues? Thanks again.

dso808 SSCrazy Points: 2102 More actions · Answer 10

Got it Craig. here it is:

SET @Where = @Where + 'AND ProbTicketSubDept LIKE ''%'' + @_Dept + ''%'' '

All single quotes of course. Seems to be working OK. Thanks again!

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 11

dso808 (12/15/2010)
Got it Craig. here it is:
SET @Where = @Where + 'AND ProbTicketSubDept LIKE ''%'' + @_Dept + ''%'' '
All single quotes of course. Seems to be working OK. Thanks again!

Ah, I'm glad you figured it out. I had meant to run a quick local test on that syntax for you and then got four phone calls, my boss doing the team lunch thing, and someone sitting on the edge of my desk. 🙂

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 12

dso808 (12/15/2010)
The above returns an incorrect syntax error. Suggestions? Thanks.

You posted in the SQL 2008 forum, so I assumed you were using SQL 2008.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dso808 SSCrazy Points: 2102 More actions · Answer 13

Shucks - not only do I need to learn SQL 2005, but I also need to learn to navigate this site better 🙂 Sorry about that...