CODEWITHSUNDEEP
sqlsql-serversql-server-2008stored-procedures
themhz
themhz

Reputation: 8424

Passing dynamic parameters to a stored procedure in SQL Server 2008

I have this procedure that executes another procedure passed by a parameter and its parameters datefrom and dateto.

CREATE procedure [dbo].[execute_proc] 
@procs varchar(200),
@pdatefrom date,
@pdateto date
as

exec @procs  @datefrom=@pdatefrom,@dateto=@pdateto

But I need to also pass the parameters dynamically without the need to edit them in the procedure. For example, what I am imagining is something like this

 CREATE procedure [dbo].[execute_proc] 
    @procs varchar(200),
    @params varchar(max)
 as
    exec @procs @params

where @params is a string like @param1=1,@param2='somethingelse'

Is there a way to do this?

Upvotes: 3

Views: 45809

Answers (2)

James E Andreasen
James E Andreasen

Reputation: 1

  1. Put the stored procedure name in a varchar field in your client table
  2. Retrieve the SP name and assign it to a parameter ( spName) when the client is chosen.

  3. In code create a function that returns a string

    function PassStoredProcedureName(spName as string) as string

    return spName

    end function

  4. Set your dataset to "Stored Procedure"

  5. Open a dataset Expression window
  6. Enter =Code.PassStoredProcedureName(Parameters!spName.value)

When you chose a client, the spName will be assigned to the parameter. When the dataset executes, it will pass the parameter to the function, which will pass the spName to the dataset.

I use this to execute custom stored procedures for clients when the same stored procedure will not work for all clients.

Be sure to normalize the aliased field names so that data retrieval to a report does not break.

Your stored procedures should always have the same parameter requirements even if they are not needed.

Upvotes: 0

Pondlife
Pondlife

Reputation: 16240

It's not really clear what the point of your wrapper procedure is (auditing? debugging?), and it seems like a very awkward solution. If you explain why you want to do this, someone may have a completely different and hopefully better solution.

The biggest issue with your proposal is that you can only pass parameters as strings and that means you have to handle all the escaping, data conversion/formatting and SQL injection issues that come with dynamic SQL. It would be much better to call each procedure directly, passing correctly typed parameters from your calling code.

Having said all that, if you really want to do it then you can do something like this:

create proc dbo.ExecuteProcedure
    @ProcedureName sysname,
    @Parameters nvarchar(max),
    @Debug bit = 0x0,
    @Execute bit = 0x1
as
set nocount on
begin

declare @sql nvarchar(max)
set @sql = 'exec ' + quotename(@ProcedureName) + ' ' + @Parameters

if @Debug = 0x1 print @sql

if @Execute = 0x1 exec(@sql)

end
go

exec dbo.ExecuteProcedure 'dbo.SomeProc', '@p1 = 1, @p2 = ''themhz''s proc''', 0x1, 0x0

You should also have a look at sp_executesql, which does almost exactly what you want, but it needs to have all the parameter data types too, which you say is not possible in your scenario.

Upvotes: 6

Related Questions