How to invoke stored procs with parameter using T-SQL scripting?

Question

We are using edge-sql to execute T-SQL scripts and also stored procedures via C#. I noticed recently that stored proc support has been added and I'm trying to execute would would be:

exec dbo.sgRouteExportDelete @TripDate='2014-05-06', @RouteId = 1234, @Action='DELETE', @Comment='xxxxxx';

in SQL Server Management Studio, using edge-sql 0.1.2.

I've played around with several variations, but I get one of 2 error messages. Either cannot find stored procedure '' or "cannot find stored procedure 'sgRouteExportDelete @TripDate='2014-05-06', @RouteId = 1234, @Action='DELETE', @Comment='xxxxxx'" The stored proc executes just fine in edge.sql when invoked via C# method.

I did some additional experimentation and found I can execute a stored proc with no parameters: exec dbo.sgVersionGet, but any stored procs with parameters return errors.

So what am I doing wrong? And how could I invoke with parameter values that aren't hard-coded, as above? Both SQL Server and edge use the @ character for parsing params.

Any help appreciated ...

-BillyB

Answer 1

In SQL Server when you are referring to a database object without the object full path ([database.schema].object), SQL Server will try to locate the object using the Default/Initial Catalog value which points to the default database if that was not specified within the connection string then chances are that when you try running your statement SQL Server won't be able to find the object throwing the "Cannot find XX" error, You should either specify an initial catalog on your connection string or execute your procedures using the full path, database.schema.procedure E.g. mydatabase.dbo.sp_customerCleanUp. On the other hand there is an internal procedure sp_executesql that you can use to run your procedures without having to hard code the parameters, all you need to do is build a string concatenating the hard coded part of the string (the procedure name) and whatever number of variables you are passing as parameters, see example ( https://technet.microsoft.com/en-us/library/ms175170%28v=sql.105%29.aspx )

Answer 2

The variables are assumed when calling a stored procedure with edge-sql. Any parameter you would preface with an '@' symbol will need to have the same name within the stored procedure.