Passing parameters to sql server stored procedures

Archived Topics
1

I have a sproc that accepts one parameter (varchar). Example parameters are 200, Cellar, Pilot, or 80a. All these will run with the exception of 80a. I have run into this in other instances with factory pmi, where it doesn’t like mixtures of characters and numbers as parameters. I can conceivably change the parameters in this instance, but would prefer getting pmi to accept mixtures. At the moment my sql query is “exec usp_xyz {cntCellar.strCellarNum}” Generally when using sql server one would put single quotes around the parameter. Is there a way to do this? TIA D. Lewis

2

In SQL query bindings, property references in curly braces are resolved and inserted into the string. To add single quotes, you just put them around the curly braces, so that this query:

exec usp_xyz '{cntCellar.strCellarNum}'

becomes this when executed

exec usp_xyz '80a'

Hope this helps,

3

Thanks Carl.

What happens when I need to append a number of parameters. The final string might look something like:

exec usp_xyz ‘abc’, ‘56799’, ‘45gg’

And those parameters might come from controls or variables on the window. I guess I am asking how do I get the commas in there?

exec dbo.usp_xyz ‘{cntCellar.strCellarNum}’ +"," + ‘{cntCellar.strsomething}’

or something along those lines? TIA D. Lewis

4

Is the number of parameters static? If so, the query would look something like this:

exec dbo.usp_xyz '{cntCellar.strCellarNum}', '{cntCellar.strsomething}'

SQL Queries bindings are much more simplistic than the expression language. (hence you don’t need the “plus” operators). They simply look at the whole query, evaluate & replace the property references in curly braces with the correct values, and carry on.

Hope this helps,

5

The number is static. I didn’t realize the sql queries weren’t the same as scripting. Thx. D