dyname paramaterized select statement

[lifesupport]

I get the following error when I run the stored procedure below. I can't see what is wrong. It's a simple select statement into a paramaterized table. Thanks

When running, it asks me to feed in a variable so I type Selstate123

Error Msg:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.

********************
Stored Procedure Code:
ALTER PROCEDURE [dbo].[SP_MakeSelStateParam]
@tablename varchar(50)
AS
BEGIN
from
SET NOCOUNT ON;

Declare @SQL VarChar(1000)

select @SQL = 'select statecode, statename, statenum, nstatenum, selection'
select @SQL = @SQL + 'into dbo.' + @tablename
select @SQL = @SQL + 'from state'

exec (@SQL)
END

 

[lifesupport]

Never mind. I got it going. I'm new to SQL and I didn't realize SQL is sensative to spacing. It didn't like the 'dbo.' either. The following works:

select @SQL = 'select statecode, statename, statenum, nstatenum, selection '
select @SQL = @SQL + 'into ' + @tablename
select @SQL = @SQL + ' from state'

 

[gmmastros]

The 'dbo.' should not have caused a problem.

The problem you had was the spacing. For the most part, SQL is not TOO sensitive to spacing, be there are some cases logical restrictions. For example, you wouldn't expect SQL to do 'the right thing' with this query.

selectstatecode,statename,statenum,nstatenum,selectionintomytblenamefromstate

When you are creating dynamic sql, you need to keep in mind that the string you are building is valid SQL. Your original code would have built a string like this:

select statecode, statename, statenum, nstatenum, selectioninto dbo.MyTableNamefrom state

One trick to keep in mind.... when you have problems with dynamic SQL, temporarily replace [blue]Exec(@SQL)[/blue] with [blue]Print @SQL[/blue] This will print the query to the message window. Usually, syntax problems are obvious when you do this. If not, you can copy/paste from message window to a new query window and try executing it. You'll get a better error message that way.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Do you need the dynamic SQL in your case at all? To me this particular SP doesn't look like you need a dynamic SQL.

 

[lifesupport]

I like the Print @SQL idea. Thanks