Dynamic - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Dynamic

Author

Topic

craigwg
Posting Yak Master

154 Posts

Posted - 2011-01-26 : 14:29:51

I have to make a change to a query that is dynamic but not sure how to do it. Here is the query:


--Debug

DECLARE @sid int, 
@token varchar(100)

SET @sid = 67732
SET @token = '12456'

--Main
DECLARE @query NVARCHAR(1000),
		@email VARCHAR(100)

SET NOCOUNT ON


IF (exists(select token from survey_67732 where token=@token)) 
	BEGIN
		SET @query = N'UPDATE tokens_' + CONVERT(VARCHAR(10),@sid) + ' SET completed = getUTCDate() WHERE token = ''' + @token + '''';

		EXEC(@query);

		SET @query = N'SELECT @email=email FROM tokens_' + CONVERT(VARCHAR(10),@sid) + ' WHERE token = ''' + @token + '''';

		EXEC sp_executesql @query, N'@email varchar(100) OUTPUT', @email OUTPUT

		SELECT @email
	END
ELSE
	BEGIN
		SELECT 'Update not possible at this time'
	END

The problem is the if statement. The token will not always be 123456 and the sid will not always be 67732. I know how to just copy and paste the other statements, but how do I execute this as part of an if statement?

Craig Greenwood

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-26 : 14:52:06

What do you mean by dynamic?

Are you looking for a stored procedure so you can pass the SID and Token? Or do you need to dynamically reference a table? Like survey_67732, but switch the 67732 based on the Token or something?

craigwg
Posting Yak Master

154 Posts

Posted - 2011-01-26 : 14:56:38

That's correct. Specifically I need the line "IF (exists(select token from survey_67732 where token=@token))" to accept that survey_67732 may be a different survey number (not 67732). It is being done in this line:

SET @query = N'UPDATE tokens_' + CONVERT(VARCHAR(10),@sid) + ' SET completed = getUTCDate() WHERE token = ''' + @token + '''';

In that case the name of the table is tokens_67732. But the concept is the same. The trick I'm running into is the if statement. If I make the If statement dynamic, I have to juggle the entire statement's apostrophes and I don't know how to do it and make it work. I hope that makes sense.

Craig Greenwood

craigwg
Posting Yak Master

154 Posts

Posted - 2011-01-26 : 15:02:29

I think this works. I wouldn't mind verification:

DECLARE @sid int,
@token varchar(15)

SET @sid = '19255'
SET @token = '12456'

--Main
DECLARE @query NVARCHAR(1000),
@email VARCHAR(100)

SET NOCOUNT ON

SET @query = N'
If (exists(select token from survey_' + CONVERT(VARCHAR(10),@sid) + ' where token=' + @token + '))
BEGIN
UPDATE tokens_' + CONVERT(VARCHAR(10),@sid) + ' SET completed = getUTCDate() WHERE token = ''' + @token + '''
SELECT @email=email FROM tokens_' + CONVERT(VARCHAR(10),@sid) + ' WHERE token = ''' + @token + '''
Select @email
END
ELSE
BEGIN
SELECT ''Update not possible at this time''
END
'
EXEC @query

Craig Greenwood

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-26 : 15:03:38

Is it too late to fix your schema?

craigwg
Posting Yak Master

154 Posts

Posted - 2011-01-26 : 15:13:00

Short answer is yes. Database is well in use with multiple applications and thousands of records. That level of realignment is not an option. This is primarily a syntactical question. I think I'm close. I can run with a print and then run the printed statement and it works great:

DECLARE @sid int,
@token varchar(15)

SET @sid = '67732'
SET @token = '12456'

--Main
DECLARE @query NVARCHAR(1000)--,
--@email VARCHAR(100)

SET NOCOUNT ON

SET @query = N' DECLARE @email varchar(100)
If (exists(select token from survey_' + CONVERT(VARCHAR(10),@sid) + ' where token=''' + @token + '''))
BEGIN
UPDATE tokens_' + CONVERT(VARCHAR(10),@sid) + ' SET completed = getUTCDate() WHERE token = ''' + @token + '''
SELECT @email=email FROM tokens_' + CONVERT(VARCHAR(10),@sid) + ' WHERE token = ''' + @token + '''
SELECT @email
END
ELSE
BEGIN
SELECT ''Update not possible at this time''
END
';
PRINT @query

But if I exec it.....OH DANG! I think I got it! I need to do EXEC(@query) with paranthesis and we're cool.

Right? It seems to be working....

Craig Greenwood

craigwg
Posting Yak Master

154 Posts

Posted - 2011-01-26 : 15:17:39

Hot steaming bag of methane! Full worky worky. Thank you to Lamprey. May all of your lights be green ones!

Craig Greenwood

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources