EXECuting a dynamic string

Question

EXECuting a dynamic string

Darryl Wilson

Mr or Mrs. 500

Points: 526
More actions
May 10, 2012 at 6:43 am

#392228

I am urgently in need of some help. My colleague and I have developed a script (that works) but uses Hard coded values. I have used the EXEC command before but not to assign a value to a variable.
eg:
SET @noOfCopies = (select SUM(NoOfCopies) as 'NumberOfCopies' FROM importedStats
Where
AuthorityCode=659 and
Category in ('AF','Jf') and Binding in ('P')
and dateImported between '01/05/2012' and '02/05/2012'
and libraryPolicyname = 'brixm'
)
The authority Code will change and I might not know the Category or the binding maybe different and it may not have an import date.
How can I build the string and assign the value to the variable
Many thanks
Darryl Wilson
darrylw99@hotmail.com

Viewing 9 posts - 1 through 8 (of 8 total)

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

drew.allen SSC Guru Points: 76985 More actions · Answer 1

You should be using sp_executesql rather than EXEC. BOL has examples of using parameters including OUTPUT parameters.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 2

Have a look at sp_executesql in BOL.

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 3

Oops, Drew beat me by a few milliseconds 😛

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

Darryl Wilson Mr or Mrs. 500 Points: 526 More actions · Answer 4

I am using the sp_ExeceuteSQL but how do i assign the value of the executed sql to a variable (so that i can use it further down the script)

Darryl Wilson
darrylw99@hotmail.com

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 5

Here is an example:

DECLARE @ReturnValue int

DECLARE @SqlStmt nvarchar ( 1024 )

DECLARE @MyParams nvarchar ( 1024 )

SET @SqlStmt = N'SELECT @CountOfRows = COUNT(*) FROM sys.objects'

SET @MyParams = N'@CountOfRows int OUTPUT'

EXEC sp_executesql @statement = @SqlStmt,

@params = @MyParams,

@CountOfRows = @ReturnValue OUTPUT

SELECT @ReturnValue

Edit: Since the parameters to sp_executesql are all Nvarchar, I might as well explicity make the constants Nvarchar as well 😉 And made the parameter names passed to sp_executesql different to the ones in the dynamic SQL statement to make it more clear what's what.

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 6

Darryl, did this help at all? If not, please come back and ask, we're all happy to assist.

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 7

None of what you seek to do actually requires any form of dynamic SQL from what I can see.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sony Francis @EY SSCommitted Points: 1539 More actions · Answer 8

Sony Francis @EY

SSCommitted

Points: 1539

May 11, 2012 at 3:00 am

#1486167

use sp_executesql