August 31, 2012 at 5:57 am
ALTER PROCEDURE [dbo].[test]
@rwcnt int output
create table ##templist
(
col1 varchar(100)
)
Above is the code i am trying to implement. It should create a global temporary table but i am getting an error that says
Incorrect syntax near the keyword 'create'.
also if i highlight the word create it says "Incorrect syntax near 'create'. Execting ',' ', ')', WITH FOR or AS
I can not seem to figure out what is causing this. I have removed the @rwcnt but it still has the error. Any help would really be appreciated. Thanks!
August 31, 2012 at 6:02 am
you are just missing the AS keyword...and global tables have double # marks (##GlbalTemp):
it's probably a good idea to put explicit BEGIN-END markers as well.
do you want to create if not exists?
ALTER PROCEDURE [dbo].[test]
@rwcnt int output
AS
BEGIN --PROC
IF (SELECT OBJECT_ID('Tempdb.dbo.##templist')) IS NULL
create table ##templist
(
col1 varchar(100)
)
END --PROC
Lowell
August 31, 2012 at 6:07 am
Thanks I knew it was something simple and dumb i was just not thinking about. And i am dropping the table everytime after the stored procedure is run so i dont need to check to see if it needs to be created.
Thanks for the help!
August 31, 2012 at 6:11 am
glad I could help!
Lowell
August 31, 2012 at 6:54 am
Potso (8/31/2012)
Thanks I knew it was something simple and dumb i was just not thinking about. And i am dropping the table everytime after the stored procedure is run so i dont need to check to see if it needs to be created.Thanks for the help!
What happens if someone else runs the stored procedure before your run has completed?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 31, 2012 at 6:56 am
this is going to be a scheduled job that is being used for a notification service so no one will ever un it besides when it is scheduled.
August 31, 2012 at 8:17 am
ChrisM@Work (8/31/2012)
Potso (8/31/2012)
Thanks I knew it was something simple and dumb i was just not thinking about. And i am dropping the table everytime after the stored procedure is run so i dont need to check to see if it needs to be created.Thanks for the help!
What happens if someone else runs the stored procedure before your run has completed?
Easy enough to find out.... try this
CREATE PROCEDURE [dbo].[test]
AS
IF (SELECT OBJECT_ID('Tempdb.dbo.##templist')) IS NULL
BEGIN
PRINT 'Created'
CREATE table ##templist
(col1 varchar(100))
WAITFOR DELAY '00:03'
END
ELSE
PRINT 'Found'
GO
August 31, 2012 at 9:03 am
I suggest not using such a simple name for a global temp table, because someone else could use the same name, and debugging the conflict would be a nightmare.
I recomend that you only use dynamically generated unique table names for global temp tables to avoid this sort of conflict.
Example table name:
##templist_BF9D1842_38AE_4CED_8432_1FA628783614
Sample of a procedure that dynamically creates a unique table name name and passes the table name back as an output parameter:
if object_id('[dbo].[test]','P') is not null begin drop procedure [dbo].[test] end
go
create procedure [dbo].[test]
(
@tablenamesysname= null output
)
as
declare @sqlnvarchar(4000)
set @tablename= N'##templist_'+replace(convert(nvarchar(100),newid()),'-','_')
set @sql =
N'
create table '+@tablename+N'
(
col1 varchar(100)
)
'
exec (@sql)
go
August 31, 2012 at 9:48 am
Potso (8/31/2012)
Thanks I knew it was something simple and dumb i was just not thinking about. And i am dropping the table everytime after the stored procedure is run so i dont need to check to see if it needs to be created.Thanks for the help!
Given this I wonder if you even need a global temp table. They tend to be very challenging to work with as many of the others have stated above. If you are dropping this table after you run your proc can you just have your proc return the data to the calling piece. That would eliminate a lot of extra effort making sure concurrency is not an issue and such.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 31, 2012 at 10:56 am
Sean Lange (8/31/2012)
Potso (8/31/2012)
Thanks I knew it was something simple and dumb i was just not thinking about. And i am dropping the table everytime after the stored procedure is run so i dont need to check to see if it needs to be created.Thanks for the help!
Given this I wonder if you even need a global temp table. They tend to be very challenging to work with as many of the others have stated above. If you are dropping this table after you run your proc can you just have your proc return the data to the calling piece. That would eliminate a lot of extra effort making sure concurrency is not an issue and such.
Unfortunately, its the most convenient solution for the frequent problem of trying to send an email notification and include query results from a temporary (or prohibitively expensive) data source, which is what I think the OP is trying to do.
Consider, if your running a periodic scan of a large table or set of tables to check for any problems. If you find any rows in your output, then you want to send a notification Email. Since the query was big and slow, you don't want the responder to have to re-query for it, so you want to include the (relatively small) output in the Email.
So how do you do all of this without having to re-execute the (big and slow) query? Well, of course you have the query dump its output to a #Temp table and then you check that table for any rows. If there are any, you send a DBMail, and use "@query='Select * From #Temp' " on the sp_send_dbmail. Except that that doesn't work. Because DBMail is actually implemented in another process that fails when it executes that query because it cannot see your #temp table.
Now you could create a permanent table for this output, but that adds a whole host of housekeeping issues of its own (what about prior rows in this table? what if a want to change the columns included? etc...). All do-able, of course, but its a lot more work for (frequently) not much gain. So often, the simplest solution is to just use a global ##Temp table. Simple fix, and it does seem to work.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 31, 2012 at 11:04 am
RBarryYoung (8/31/2012)
Sean Lange (8/31/2012)
Potso (8/31/2012)
Thanks I knew it was something simple and dumb i was just not thinking about. And i am dropping the table everytime after the stored procedure is run so i dont need to check to see if it needs to be created.Thanks for the help!
Given this I wonder if you even need a global temp table. They tend to be very challenging to work with as many of the others have stated above. If you are dropping this table after you run your proc can you just have your proc return the data to the calling piece. That would eliminate a lot of extra effort making sure concurrency is not an issue and such.
Unfortunately, its the most convenient solution for the frequent problem of trying to send an email notification and include query results from a temporary (or prohibitively expensive) data source, which is what I think the OP is trying to do.
Consider, if your running a periodic scan of a large table or set of tables to check for any problems. If you find any rows in your output, then you want to send a notification Email. Since the query was big and slow, you don't want the responder to have to re-query for it, so you want to include the (relatively small) output in the Email.
So how do you do all of this without having to re-execute the (big and slow) query? Well, of course you have the query dump its output to a #Temp table and then you check that table for any rows. If there are any, you send a DBMail, and use "@query='Select * From #Temp' " on the sp_send_dbmail. Except that that doesn't work. Because DBMail is actually implemented in another process that fails when it executes that query because it cannot see your #temp table.
Now you could create a permanent table for this output, but that adds a whole host of housekeeping issues of its own (what about prior rows in this table? what if a want to change the columns included? etc...). All do-able, of course, but its a lot more work for (frequently) not much gain. So often, the simplest solution is to just use a global ##Temp table. Simple fix, and it does seem to work.
Sure for DBMail it works well. I just never saw anything in here that indicated that. Global temp tables can just a pain to deal with so unless there is a valid reason (like the one you mentioned for DBMail) it is usually easier to not use them.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 31, 2012 at 11:08 am
Sean Lange (8/31/2012)
...Sure for DBMail it works well. I just never saw anything in here that indicated that. Global temp tables can just a pain to deal with so unless there is a valid reason (like the one you mentioned for DBMail) it is usually easier to not use them.
This is what made me think that:
Potso (8/31/2012)
this is going to be a scheduled job that is being used for a notification service so no one will ever un it besides when it is scheduled.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 31, 2012 at 11:15 am
RBarryYoung (8/31/2012)
Sean Lange (8/31/2012)
...Sure for DBMail it works well. I just never saw anything in here that indicated that. Global temp tables can just a pain to deal with so unless there is a valid reason (like the one you mentioned for DBMail) it is usually easier to not use them.
This is what made me think that:
Potso (8/31/2012)
this is going to be a scheduled job that is being used for a notification service so no one will ever un it besides when it is scheduled.
Ahh, the devil is in the details huh? 😀
Well I still say that if you don't need a global temp table you shouldn't use it (which is probably not the case here).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 31, 2012 at 11:24 am
Sean Lange (8/31/2012)
...Ahh, the devil is in the details huh? 😀
Indeed.
Well I still say that if you don't need a global temp table you shouldn't use it (which is probably not the case here).
Agreed. On both counts. :-D:-D
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply