quote:

---

Originally posted by nmarks

I cannot see the wood for the trees. Please help!

I have written a large application and my boss requires version control for every table, procedure and function.

I intend to use sp_executesql, the built function, to allow me to call procedures by the name held in a variable.

For example : Execute @version_of_product_pricing

where the value of @version_of_sp_some_proc might be sp_some_proc_1_0_0 this week but change to sp_some_proc_1_0_1 next week.

The problem I'm having is accessing the result variable in some of the procedures.

The simplest example is this:

ALTER PROCEDURE [dbo].[sp_maximum](
@A float,
@B float,
@max float output)

AS
BEGIN

IF @A>=@B
BEGIN
set @max = @A
END
ELSE
BEGIN
set @max = @B
END

END

I'n trying to return the @max value from this procedure using the sp_executesql method using the following technique:

ALTER PROCEDURE [dbo].[Test_Multiple_Params]
AS
BEGIN

DECLARE @intA float;
DECLARE @intB float;
DECLARE @intmax float;
DECLARE @result float;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);

SET @SQLString = N'EXEC sp_maximum @A , @B, @max';
SET @ParmDefinition = N'@A FLOAT, @B FLOAT, @max FLOAT OUTPUT';

SET @intA=1;
SET @intB=2;
SET @intmax = 0;

EXECUTE sp_executesql @SQLstring, @ParmDefinition, @A = @intA, @B = @intB, @max = @intmax output;

SELECT @intmax

END

This produces an output of zero, which of course it shouldn't.

Can you see the error?

---

quote:

---

Originally posted by nmarks

Thanks for trying.

I commented out the statement, ran the procedure and got NULL (presumably because @intmax hasn't been initialised).

I also tried changing 'SELECT @intmax' for 'SELECT @max' and got the error 'Must declare the scalar variable "@max"'.

---

ALTER PROCEDURE [dbo].[Test_Multiple_Params]
AS
BEGIN
	DECLARE @intA float;
	DECLARE @intB float;
	DECLARE @intmax float;
	DECLARE @result float;
	DECLARE @SQLString nvarchar(500);
	DECLARE @ParmDefinition nvarchar(500);

	SET @SQLString = N'EXEC sp_maximum @A , @B, @MAX';
	SET @ParmDefinition = N'@A float, @B float, @MAX float OUTPUT';

	SET @intA=1;
	SET @intB=2;
	SET @intmax = 0;

	EXECUTE sp_executesql @SQLstring, @ParmDefinition, @A = @intA, @B = @intB, @MAX = @intmax OUTPUT;
	EXEC sp_maximum @A = @intA, @B = @intB, @MAX = @intmax OUTPUT

	SELECT @intmax
END

ALTER PROCEDURE [dbo].[Test_Multiple_Params]
AS
BEGIN

	DECLARE @intA float;
	DECLARE @intB float;
	DECLARE @intmax float;
	DECLARE @result float;
	DECLARE @SQLString nvarchar(500);
	DECLARE @ParmDefinition nvarchar(500);

	SET @SQLString = N'EXEC sp_maximum @A , @B, @MAX OUTPUT';
	SET @ParmDefinition = N'@A float, @B float, @MAX float OUTPUT';

	SET @intA=1;
	SET @intB=2;
	SET @intmax = 0;

	EXECUTE sp_executesql @SQLstring, @ParmDefinition, @A = @intA, @B = @intB, @MAX = @intmax OUTPUT;

	SELECT @intmax

END