SQL Server : procedure call, inline concatenation impossible?

Question

I'm using SQL Server 2008 R2 and was wondering if there is another way of writing something like

EXEC dbo.myProcedure (SELECT columnName FROM TableName)

or

EXEC dbo.myProcedure @myStringVariable + 'other text'

so that these procedure calls actually work, without putting the whole stuff into a variable first.

possible duplicate of [Cast integer and concatenate to varchar in TSQL](http://stackoverflow.com/questions/4936180/cast-integer-and-concatenate-to-varchar-in-tsql) — Martin Smith, Aug 19 '12 at 13:15
No it isn't possible. The stored procedure parameter list can only include literals, variables, parameters, and `@@` system functions. [Vote for this here](https://connect.microsoft.com/SQLServer/feedback/details/352110/t-sql-use-scalar-functions-as-stored-procedure-parameters) — Martin Smith, Aug 19 '12 at 13:16

score 1 · Answer 1 · edited Jan 22 '13 at 08:45

1

Concatenation in inline execution of SP is not allowed..

Assuming that your @myStringVariable compromises of certain text and certain dynamic text you want to concatenate it.

Why don't compute it and assign to @myStringVariable before making the call to proc.

edited Jan 22 '13 at 08:45

Jon Egerton

40,401
11
97
129

answered Jan 22 '13 at 08:25

mvm_bgm

153
1
11

score 0 · Answer 2 · answered Dec 20 '12 at 21:59

Not sure what you mean by "without putting the whole stuff into a variable first". However, I have a solution, even though it may go against what I think you mean. But here it is anyway.

DECLARE @SQL VARCHAR(MAX)

SET @SQL = ''

SELECT
    @SQL = @SQL + 'EXEC SprocName ''' + ColumnName + ''''
FROM
    MyTable 

EXEC (@SQL)

I use this method all the time, when I need to execute a sproc based on specific values from a table.

SQL Server : procedure call, inline concatenation impossible?

2 Answers2