Dynamic SQL Variables - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Dynamic SQL Variables

Author

Topic

JAdauto
Posting Yak Master

160 Posts

Posted - 2014-08-12 : 19:02:59

I am trying to write a Dynamic SQL statement and I either get an error about the scalar variables need to be declared or I get a syntax error (presumably due to my quotes).

Below is a small section of my code. When I run the section that is commented out, it works fine. When I comment that out and run my Set @SQLCmd code (along with the print and Exec) it fails.

Any suggestions? I am well into two days on just trying to get this syntax to work. Something so easy is so hard.

Thanks
JA

DECLARE @SQLCmd nvarchar(4000)
DECLARE @i int
DECLARE @ErrorMessage nvarchar(500)
DECLARE @TableName nvarchar(200)
DECLARE @Original_CompanyID nvarchar(100)
DECLARE @Original_StoreID int
DECLARE @Original_Server nvarchar(100)
DECLARE @New_CompanyID nvarchar(100)
DECLARE @New_StoreID int
DECLARE @New_Server nvarchar(100)

SET @i = 1
SET @ErrorMessage = ''
SET @Original_CompanyID = 'xyz01'
SET @Original_StoreID = 1
SET @New_CompanyID = 'abc01'
SET @New_StoreID = 101
SET @Original_Server = 'Server01'
SET @New_Server = 'Server02'

CREATE TABLE #TablesList (TableName nvarchar(100), FieldName nvarchar(100))
CREATE TABLE #CheckCommands (UniqueID int IDENTITY(1,1), CheckCommand nvarchar(4000), TableName nvarchar(200))
CREATE TABLE #UpdateCommands (UniqueID int IDENTITY(1,1), UpdateCommand nvarchar(4000), TableName nvarchar(200), FieldName nvarchar(50))CREATE TABLE #Errors (UniqueID int IDENTITY(1,1), Error varchar(500))

INSERT INTO #TablesList VALUES ('SubMenu', 'Owner')
INSERT INTO #TablesList VALUES ('SubMenuItem', 'Owner')

--Good @SQLCmd syntax without quote issues - can uncomment this and comment the @SQLCmd lines and run to see results
INSERT INTO #CheckCommands (CheckCommand, TableName)
SELECT 'IF EXISTS (SELECT TOP 1 1 FROM [' + @Original_Server + '].' + @Original_CompanyID + '.dbo.' + a.[Name] + ' WHERE ' + b.[Name] + ' = ' + CAST(@New_StoreID AS varchar(10)) + ')
INSERT INTO #Errors (Error) VALUES (''Table [' + @Original_Server + '].' + @Original_CompanyID + '.dbo.' + a.[Name] + ' already has data in it for that Store ID'') ' AS CheckCommand,
a.[Name]
FROM Sys.Objects AS a
JOIN Sys.Columns AS b
ON a.Object_ID = b.Object_ID
JOIN #TablesList c
ON a.Name = c.TableName
AND b.name = c.FieldName
WHERE [Type] = 'U'

--dynamic SQL statement that is not working.
SET @SQLCmd = 'INSERT INTO #CheckCommands (CheckCommand, TableName)
SELECT ''IF EXISTS (SELECT TOP 1 1 FROM ['' + @Original_Server + ''].'' + @Original_CompanyID + ''.dbo.'' + a.[Name] + '' WHERE '' + b.[Name] + '' = '' + CAST(@New_StoreID AS varchar(10)) + '')
INSERT INTO #Errors (Error) VALUES ('''Table ['' + @Original_Server+ ''].'' + @Original_CompanyID + ''.dbo.'' + a.[Name] + '' already has data in it for that Store ID''') '' AS CheckCommand,
a.[Name]
FROM Sys.Objects AS a
JOIN Sys.Columns AS b
ON a.Object_ID = b.Object_ID
JOIN #TablesList c
ON a.Name = c.TableName
AND b.name = c.FieldName
WHERE [Type] = ''U'''

print @SQLCmd
EXEC @SQLCmd

Select * from #CheckCommands

DROP TABLE #CheckCommands
DROP TABLE #UpdateCommands
DROP TABLE #Errors
DROP TABLE #TablesList

--results expected in CheckCommand column:
--IF EXISTS (SELECT TOP 1 1 FROM [Server01].xyz01.dbo.SubMenu WHERE Owner = 101) INSERT INTO #Errors (Error) VALUES ('Table [Server01].xyz01.dbo.SubMenu already has data in it for that Store ID')
--IF EXISTS (SELECT TOP 1 1 FROM [Server01].xyz01.dbo.SubMenuItem WHERE Owner = 101) INSERT INTO #Errors (Error) VALUES ('Table [Server01].xyz01.dbo.SubMenuItem already has data in it for that Store ID')

JAdauto
Posting Yak Master

160 Posts

Posted - 2014-08-12 : 19:03:52

I should add to that we have clients who still run SQL2005, 2008 and 2012, so I need to make sure whatever solution I find, works for all servers. Thanks again.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-12 : 19:18:23

I'm confused why you can't just use the INSERT/SELECT that is working.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

JAdauto
Posting Yak Master

160 Posts

Posted - 2014-08-14 : 19:25:55

DOH! You are so correct.

Sometimes the darn trees get in the way for the forest. Thank you.

Next questions. Now I need the same type of thing, but with an if Exists:

SET @i = 1
SET @ErrorMessage = ''
SET @Original_CompanyID = 'xyz01'
SET @Original_StoreID = 1
SET @New_CompanyID = 'abc01'
SET @New_StoreID = 101
SET @Original_Server = 'Server01'
SET @New_Server = 'Server02'

IF NOT EXISTS ('SELECT StoreID FROM [' + @Original_Server + '].' + @Original_CompanyID + '.dbo.GblStore WHERE StoreID = ' + CAST(@Original_StoreID AS varchar(10)) + ')'
BEGIN
SET @ErrorMessage = 'Store ID ' + CAST(@Original_StoreID AS varchar(10)) + ' Does not exist on the website for ' + CAST(@Original_CompanyID AS varchar(10)) + '!'
RAISERROR(@ErrorMessage,15,1)
GOTO Quit
END

Incorrect syntax by Exists

I have tried moving my ', not having ', putting the [ ] on my server name set statements, setting the Select to a dynamic SQL statement and running
If NOT Exists (EXEC (@SelCmd)
I cannot get this part right and I have two of these sections.

See any trees or forests in my way?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-14 : 19:45:29

What are you trying to achieve with this? Is the IF NOT EXISTS supposed to go into the temp table? Please provide more info.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

JAdauto
Posting Yak Master

160 Posts

Posted - 2014-08-15 : 11:37:07

I am running some checks before I do a ton of data "moves" updating one store ID to another store ID. this happens when one store buys out another store, etc. One of the checks is above -> if the store ID does not exist in the original company, then I will need to exit this process as I will not have any data to update from. Also, if the new store ID already exists on the new company, then I will need to exit. The problem is that they have to provide the original and new server Ids, company IDs and store IDs. That If NOT Exists works great for data moves in the same server and company (example :Store ID 1 is moving to Store ID 100). However, I have to make this work across server and db names (Example: Store ID 1 from Server1.abc01.dbo.gblstore needs to move to StoreID 100 from Server2.xyz01.dbo.gblstore (plus 169 other tables), which is why I am trying to check

If Not Exists (Select Store ID from Server1.abc01.dboGlobalStore where StoreiD = 1)

but with parameters for my values so the tech can enter those values.

Does that make sense for helpful?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-15 : 13:56:03

You'll need to use dynamic SQL for that.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

JAdauto
Posting Yak Master

160 Posts

Posted - 2014-08-15 : 14:32:52

Yes. I am trying to do Dynamic SQL, but I get errors.

DECLARE @SQLCmd varchar(4000)
DECLARE @i int
DECLARE @ErrorMessage varchar(500)
DECLARE @TableName varchar(200)
DECLARE @Original_CompanyID varchar(100)
DECLARE @Original_StoreID int
DECLARE @Original_Server varchar(100)
DECLARE @New_CompanyID varchar(100)
DECLARE @New_StoreID int
DECLARE @New_Server varchar(100)

SET @i = 1
SET @ErrorMessage = ''
SET @Original_CompanyID = 'dev01'
SET @Original_StoreID = 1
SET @New_CompanyID = 'dev01'
SET @New_StoreID = 1508
SET @Original_Server = 'dfw05-dv1sql109'
SET @New_Server = 'dfw05-dv1sql109'

DECLARE @Sel nvarchar (4000)

SET @Sel =
'IF NOT EXISTS (SELECT StoreID FROM [' + @Original_Server + '].' + @Original_CompanyID + '.dbo.GblStore WHERE StoreID = ' + CAST(@Original_StoreID AS varchar(10)) + ')
BEGIN
SET @ErrorMessage = ''Store ID '' + CAST(@Original_StoreID AS varchar(10)) + '' Does not exist on the website for '' + CAST(@Original_CompanyID AS varchar(10)) +''!''
RAISERROR(@ErrorMessage,15,1)
GOTO Quit
END'

print (@SEL)
print (@ErrorMessage)
EXEC (@SEL)

Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@Original_StoreID".
Msg 137, Level 15, State 2, Line 4
Must declare the scalar variable "@ErrorMessage".

Ideas?

JAdauto
Posting Yak Master

160 Posts

Posted - 2014-08-15 : 14:39:06

DECLARE @SQLCmd varchar(4000)
DECLARE @i int
DECLARE @ErrorMessage varchar(500)
DECLARE @TableName varchar(200)
DECLARE @Original_CompanyID varchar(100)
DECLARE @Original_StoreID int
DECLARE @Original_Server varchar(100)
DECLARE @New_CompanyID varchar(100)
DECLARE @New_StoreID int
DECLARE @New_Server varchar(100)

SET @i = 1
SET @ErrorMessage = ''
SET @Original_CompanyID = 'dev01'
SET @Original_StoreID = 1
SET @New_CompanyID = 'dev01'
SET @New_StoreID = 1508
SET @Original_Server = 'dfw05-dv1sql109'
SET @New_Server = 'dfw05-dv1sql109'

DECLARE @Sel nvarchar (4000)

SET @Sel =
'IF NOT EXISTS (SELECT StoreID FROM [' + @Original_Server + '].' + @Original_CompanyID + '.dbo.GblStore WHERE StoreID = ' + CAST(@Original_StoreID AS varchar(10)) + ')
BEGIN
DECLARE @ErrorMessage varchar(500)
SET @ErrorMessage = ''Store ID ' + CAST(@Original_StoreID AS varchar(10)) + ' Does not exist on the website for ' + CAST(@Original_CompanyID AS varchar(10)) + '.''
RAISERROR(@ErrorMessage,15,1)
GOTO Quit
END'

print (@SEL)
print (@ErrorMessage)
EXEC (@SEL)

Quit:
--clean up code

Now I think I am making progress, but I get this error:

A GOTO statement references the label 'Quit' but the label has not been declared.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-15 : 15:31:13

Dynamic SQL does not execute in the same session as the rest of your code, so it can't see the Quit label or anything else. I would probably put the SELECT into a temp table using dynamic SQL (insert into #temp exec (@sql)) and then do the if not exists/everything else.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

JAdauto
Posting Yak Master

160 Posts

Posted - 2014-08-15 : 16:55:23

Can I do this:

SET @SQLCmd = 'FROM (SELECT * FROM [ ' + @Original_Server + ' ].' + @Original_CompanyID + '.dbo.GblStore WHERE StoreID = ' + CAST(@Original_StoreID AS varchar(10)) + ')'
SELECT * INTO #OldStoreExists
EXEC (@SQLCmd)

it gives me error saying: Must specify table to select from.

JAdauto
Posting Yak Master

160 Posts

Posted - 2014-08-15 : 17:02:34

Here is the whole section:

DECLARE @SQLCmd varchar(4000)
DECLARE @i int
DECLARE @ErrorMessage varchar(500)
DECLARE @TableName varchar(200)
DECLARE @Original_CompanyID varchar(100)
DECLARE @Original_StoreID int
DECLARE @Original_Server varchar(100)
DECLARE @New_CompanyID varchar(100)
DECLARE @New_StoreID int
DECLARE @New_Server varchar(100)

SET @i = 1
SET @ErrorMessage = ''
SET @Original_CompanyID = 'dev01'
SET @Original_StoreID = 3111
SET @New_CompanyID = 'dev01'
SET @New_StoreID = 1508
SET @Original_Server = 'dfw05-dv1sql109'
SET @New_Server = 'dfw05-dv1sql109'

---- Make sure the original store ID actually exists on the website. --Populate #OldStoreExists for use later
SET @SQLCmd = 'FROM (SELECT * FROM [ ' + @Original_Server + ' ].' + @Original_CompanyID + '.dbo.GblStore WHERE StoreID = ' + CAST(@Original_StoreID AS varchar(10)) + ')'
SELECT * INTO #OldStoreExists
EXEC (@SQLCmd)

IF NOT EXISTS (Select * from #OldStoreExists)
BEGIN
SET @ErrorMessage = 'Store ID ' + CAST(@Original_StoreID AS varchar(10)) + ' Does not exist on the website for ' + CAST(@Original_CompanyID AS varchar(10)) + '.'
RAISERROR(@ErrorMessage,15,1)
GOTO Quit
END

--populate #NewStoreExists for use later
SET @SQLCmd = 'FROM SELECT * FROM [ ' + @New_Server + ' ].' + @New_CompanyID + '.dbo.GblStore WHERE StoreID = ' + CAST(@New_StoreID AS varchar(10))
SELECT * INTO #NewStoreExists
EXEC (@SQLCmd)

Select * from #oldStoreExists
Select * from #NewStoreExists

QUIT:

I have some pieces of code that I could later use data in these temp tables if it exists. So I wanted to just insert the GblSTore record into that temp table if it exists.

But SQL is balking at me. I am sure that I am rushing through this. I continue to make dumb errors or make it more complicated then i need to. And now I am behind schedule. Ouch.

Msg 263, Level 16, State 1, Line 25
Must specify table to select from.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-15 : 17:12:43

You can't do this:
---- Make sure the original store ID actually exists on the website. --Populate #OldStoreExists for use later
SET @SQLCmd = 'FROM (SELECT * FROM [ ' + @Original_Server + ' ].' + @Original_CompanyID + '.dbo.GblStore WHERE StoreID = ' + CAST(@Original_StoreID AS varchar(10)) + ')'
SELECT * INTO #OldStoreExists
EXEC (@SQLCmd)

You need to create a temp table explicitly and then insert into/exec the dynamic sql.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources