How to concatenate a stored procedure parameter inside a SQL Server string query?

Question

I am trying to concatenate a parameter of my stored procedure inside a query but it always keeps showing an error . This my query:

EXEC xp_cmdshell 'bcp "SELECT matriculeEmployeur,
                              cleEmployeur,
                              codeDexp,
                              trimestre,
                              annee,
                              page,
                              ligne,
                              matriculeAssure,
                              cleAssure,
                              CONCAT(CONCAT(nom,nomPere,prenom),space(60-LEN(CONCAT(nom,nomPere,prenom)))),
                              carteIdentity,
                              salaire 
                              FROM ##myTempo" queryout "FILEPATH"' +fname+ '-c -T'.

The error is shown on the first concatenation symbol [the first plus sign]. What error did I make here?

Maybe this is because fname without an @ in front is not a valid parameter. — R. Richards
– R. Richards, Commented Sep 17, 2017 at 17:20

TT. · Accepted Answer · 2017-09-17 19:00:22Z

6

First construct the command in a VARCHAR variable, then use the variable in your exec statement. You can't concatenate strings in place of a stored procedure parameter.

DECLARE @cmd VARCHAR(8000);
SET @cmd=...; -- build your command shell command here
EXEC xp_cmdshell @cmd;

answered Sep 17, 2017 at 19:00

TT.

16.2k6 gold badges53 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to concatenate a stored procedure parameter inside a SQL Server string query?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related