I didn't read beyond the CREATE PROCEDURE. It wasn't the first line of the batch so I assumed "procedure would not run" would be the answer.
Excellent question. I came up with five or six possible reasons why the SP might not work, and ruled each of them out in turn. Of course I never thought of that one! Glad to have learned something this morning.
Ron Moses
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
Iulian -207023 (11/18/2010)
Do you have at hand some best practices for using DML statements? I am thinking on something like:
...
SET @strQueryDML = 'SELECT ID INTO #tmpID2 FROM tbl1'
...
EXECUTE (@strQueryDML)
...
SELECT ID FROM #tmpID2
This code fails with the error message "Invalid object name #tmpID2". In this case, the local temprorary table "#tmpID2" exists only within the scope of the dynamic SQL. When the execution of the dynamic SQL is complete, SQL Server deletes the table. Of course, you may move the SELECT statement into the D-SQL piece of code, but it would be (to put it mildly) not the best decision
Excellent question on a topic which is such a problem. I see this a lot from developers who wonder why it won't work. At first glance, it looks like it shuld and programatically should. But then apply logic;-)
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
Great question. Keep them coming!!
_______________________________________________________________
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/
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply