Dynamic Queries - Can this be done

Question

Post reply

Dynamic Queries - Can this be done

Bob Johnson-275952

Grasshopper

Points: 16
More actions
November 8, 2005 at 2:40 pm

#88483

I am attempting to dynamically create a Query, but to no avail. Is there a way to store a query in a string and use the EXEC(@STRING) command while still using variables from the stored procedure. I know it will not work the way I have it due to scope issues, but does anyone have any help on how to make this work while still allowing to dynamically generate the Query.
SET ANSI_NULLS OFF
GO
If Exists (select * from sysobjects where id = object_id ('dbo.ABC') and Type = 'P')
Drop Procedure dbo.ABC
GO
Create PROCEDURE [dbo].[ABC]
@ID int,
@TableId int,
@StartDt datetime
AS
CREATE TABLE #CursorTemp(ID int IDENTITY, RebateId int)
DECLARE
@a int,
@b-2 datetime,
@C int,
@D varchar(20),
@Query varchar(2000),
@QueryWhere varchar(1000),
@CursorInt int
SELECT @Query = 'SELECT RebateId from Rebates '
SELECT @QueryWhere = ' Where @StartDt < RebateStartDate ' SELECT @a = a,
@b-2 = b,
@C = c,
@D = d
FROM OtherTable
WHERE @ID = OtherTableID
--Check for NULL in Columns A and B
IF @a <> null
BEGIN
SET @QueryWhere = @QueryWhere + 'AND @a = a '
END
IF @b-2 <> null
BEGIN
SET @QueryWhere = @QueryWhere + 'AND @b-2 = b '
END
SELECT @Query = @Query + @QueryWhere
EXEC(@Query)

Viewing 12 posts - 1 through 11 (of 11 total)

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

Wayne Lawton Hall of Fame Points: 3532 More actions · Answer 1

G'day,

This is without query analyzer, so check the syntax...

SELECT @Query = 'SELECT RebateId from Rebates '

SELECT @QueryWhere = ' Where @StartDt < RebateStartDate '

should be something like

SELECT @Query = 'SELECT RebateId from Rebates '

SELECT @QueryWhere = ' Where ' + CAST (@StartDt AS VARCHAR) + ' < RebateStartDate '

You will need to make similar changes to the remainder of your query.

Hope this helps

Wayne

john-132685 SSCertifiable Points: 6134 More actions · Answer 2

he forgot the embedded quotes necessary around the converted date

Wayne Lawton Hall of Fame Points: 3532 More actions · Answer 3

Wayne Lawton

Hall of Fame

Points: 3532

November 8, 2005 at 2:58 pm

#602906

Thanks John!

Have a great day

Wayne

Bob Johnson-275952 Grasshopper Points: 16 More actions · Answer 4

Thanks for all your replies, however I Have tried implementing your solution, but again I am getting an error telling me I have to Declare the variable... The SQL Statement looks like this

SELECT @QuerySelect = 'INSERT #CursorTemp Select RebateId'

SELECT @QueryFrom = ' From RebateAgreements'

SELECT @QueryWhere = @QueryFrom + ' Where ' + Cast('@AgreementSetId' AS VARCHAR)+ ' = AgreementSetId'

SELECT @QueryWhere = @QueryWhere + ' AND RebateStartDt = ' + CAST('@SearchStartDt' AS VARCHAR)

SELECT @Query = @QuerySelect + @QueryWhere

EXEC(@Query)

Here is the error message, which is the same as the one I recieved before

Server: Msg 137, Level 15, State 2, Line 1

Must declare the variable '@AgreementSetId'.

Wayne Lawton Hall of Fame Points: 3532 More actions · Answer 5

SELECT @QueryWhere = @QueryFrom + ' Where ' + Cast('@AgreementSetId' AS VARCHAR)+ ' = AgreementSetId'

should be

SELECT @QueryWhere = @QueryFrom + ' Where ''' + Cast(@AgreementSetId AS VARCHAR)+ ''' = AgreementSetId'

and so on. The tick marks need to be moved slightly. Also note that the tick marks are two single quote marks - not a double quote.

Wayne

nileshsane Default port Points: 1465 More actions · Answer 6

My apologies for posting what I am about to post on here, though it is not a solution to the original post never the less it is along the lines of dynamic SQL so I thought I'd ask it in the same post.

The size of the variable (in the example its @query) is limited to 8000 chars for SQL Server 2000. What if I have a query which exceeds more then 8000 chars, whats the option. The query will obviosly fail in that case. How can one overcome the variable size difference?

Does Exec(@query) work in the same fashion as Execute SP_executesql @query?

Thanks in advance.

What I hear I forget, what I see I remember, what I do I understand

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 7

Noone has yet asked the obvious question...

Why? Why, why, why... Why do you have to use dynamic SQL..?

Have you read this? http://www.sommarskog.se/dynamic_sql.html

It's highly recommended reading for all who even thinks the thought 'dynamic' when writing SQL code..

/Kenneth

Aswani kumar Mr or Mrs. 500 Points: 556 More actions · Answer 8

For this Question:

Does Exec(@query) work in the same fashion as Execute SP_executesql @query?

May be u know,ifor this SP_executesql @query the paramter must be nvarchar.Nvarchar size is 4000.

David Burrows SSC Guru Points: 65142 More actions · Answer 9

My apologies for posting what I am about to post on here...

EXEC(@querypart1 + @querypart2 + @querypart3 + @querypart4)

Far away is close at hand in the images of elsewhere.
Anon.

nileshsane Default port Points: 1465 More actions · Answer 10

Thanks David. Was so simple...

What I hear I forget, what I see I remember, what I do I understand

Calvin Lawson SSChampion Points: 11030 More actions · Answer 11

You are using a temporary table, dynamic SQL, and a cursor. These are all three big no-nos, unless you have an extremely valid reason. I'm guess you could make this query much much better with a little set based thinking.

That said, when debugging dynamic SQL, simply use the print statement and try running the code manually. Works like a charm, and you would have easily found your problem.

declare @QuerySelect varchar(2000),

@QueryFromvarchar(2000),

@QueryWherevarchar(2000),

@Query varchar(2000)

SELECT @QuerySelect = 'INSERT #CursorTemp Select RebateId'

SELECT @QueryFrom = ' From RebateAgreements'

SELECT @QueryWhere = @QueryFrom + ' Where ' + Cast('@AgreementSetId' AS VARCHAR)+ ' = AgreementSetId'

SELECT @QueryWhere = @QueryWhere + ' AND RebateStartDt = ' + CAST('@SearchStartDt' AS VARCHAR)

SELECT @Query = @QuerySelect + @QueryWhere

print @Query

Signature is NULL