Hi,

I have thef ollowing EXEC statem nt in a stored rpocedure.

EXEC('Select' +@OutID+ '= TestVorgangID from ' + @tableName)

It reports incorrect syntax near =. I have tried everything and no luck. Any help would be greatly appareciated.

Thanks,
A tired Pankaj

Hi
Have you tried - does this work??
EXEC('Select * from ' + @tableName)

Ian

What is @OutID? What's its value?

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

You may just need a space after Select

 ... and before the equal sign as follows:



Rand

Thanks guys. I will try it out.

Pankaj

Nope, still no luck :-( Here is the whole procedure

CREATE PROCEDURE UpdateTestVorgaenge
    @tableName nVarchar (250),
    @TestName nVARCHAR(250),
    @OutID int OUTPUT

AS
EXEC('INSERT INTO ' + @tableName + ' (Name) values ("' + @TestName + '" ) ')
EXEC('Select ' + @OutID + ' = TestVorgangID from ' + @tableName)

The surprising thing is the first statement seems to execute just fine. The second line always gives this error on execution...Incorrect syntax near =.

Place your output parameter inside the quotes:
Sometimes it's helpful to PRINT the string you are trying to execute; if you had, you would have seen the problem...

Note that this query will still likely fail, though for a different reason, unless there is only one row in the table whose name you are passing as a parameter.

Edit: Now that I think about it, this query still won't work, as I'm sure there will be variable scoping problems. That is, the variable @OutID inside the exec is at a different scope that then the @OutID in the stored procedure.

See BOL for sp_executesql for an example of how to return a value via dynamic SQL.

See also http://p2p.wrox.com/topic.asp?TOPIC_ID=4534 from some security pitfalls using dynamic sql.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

are you just trying to get the new records identity?
Try

CREATE PROCEDURE UpdateTestVorgaenge
    @tableName nVarchar (250),
    @TestName nVARCHAR(250),
    @OutID int OUTPUT
AS
    EXEC('INSERT INTO ' + @tableName + ' (Name) values ("' + @TestName + '" ) ')
    SET @OutID = @@Identity