error in stored procedure

[simonakiki]

hi this is my stored procedure in sql server 2005

this stored procedure select data from database (whith search criteria and sort data by @order_by send by caller and default value is sr_id


when i execute this procedure this error will appear "Must declare the scalar variable "@p_result". " and i don't know why please if u can help me

ALTER PROCEDURE [dbo].[GridView_Sort] (
        @p_version nvarchar(100) = null,                               @p_result nvarchar(100) = null,
        @Salesman int = null ,                               @from_Date datetime = null ,
        @to_date datetime = null,
        @order_by nvarchar(100) = null)
 
 
 
AS
BEGIN
DECLARE @p_build nvarchar(max);
 
if @order_by is null
SET @order_by = ' order BY sr_id';
 
 
SET @p_build = 'SELECT  sr_id AS ID,
sr_version AS Version,
sm_name AS SalesMan_Names,
CASE sr_result WHEN 1 THEN ''OK'' ELSE ''KO'' END  AS Result,
sr_comments AS Comments,
sr_datetime  AS Date 
FROM  SYNC_RESULT ,SALESMAN 
WHERE sm_id = sr_sm_id and
 ((@p_version IS NOT NULL AND sr_version = @p_version) OR (@p_version IS NULL)) 
and ((@p_result IS NOT NULL AND sr_result = @p_result) OR (@p_result IS NULL))
 and ((@Salesman IS NOT NULL AND sm_id = @Salesman) OR (@Salesman IS NULL))
 and ((@from_Date IS NOT NULL AND CONVERT(nvarchar(32),sr_datetime, 112) >= CONVERT(nvarchar(32),@from_Date, 112))  OR (@from_Date IS NULL)) 
and ((@to_date IS NOT NULL AND CONVERT(nvarchar(32),sr_datetime, 112) <= CONVERT(nvarchar(32),@to_date, 112))  OR (@to_date IS NULL))' + @order_by 
 
EXEC sp_executesql @p_build ;
 
 
END

[Plater]

You have a variable called @p_result declared in your stored procedure....it's also used in your query.
You have to assin all variables to use the stored procedure

ALTER PROCEDURE [dbo].[GridView_Sort] (
@p_version nvarchar(100) = null, 
@p_result nvarchar(100) = null,
@Salesman int = null , @from_Date datetime = null ,
@to_date datetime = null,
@order_by nvarchar(100) = null)
 

[simonakiki]

You have a variable called @p_result declared in your stored procedure....it's also used in your query.
You have to assin all variables to use the stored procedure

ALTER PROCEDURE [dbo].[GridView_Sort] (
@p_version nvarchar(100) = null, 
@p_result nvarchar(100) = null,
@Salesman int = null , @from_Date datetime = null ,
@to_date datetime = null,
@order_by nvarchar(100) = null)
 

how can assin this variables??????????????/

[Plater]

how can assin this variables??????????????/

The same way you assigned all the others, like @p_version

Are you getting this error when trying to execute the procedure or when you're creating it?

[simonakiki]

The same way you assigned all the others, like @p_version

Are you getting this error when trying to execute the procedure or when you're creating it?

man the problem occure when i test this stored procedure

but if i execute query without @p_build and EXEC sp_executesql @p_build ; it work fine but it's not the solution coz i have to pass by parameters the order by and i have to create the query like a nvarchar and after execute it by EXEC sp_executesql

so i don't know what have to do

hey in testing i deleted
and ((@p_version IS NOT NULL AND sr_version = @p_version) OR (@p_version IS NULL))

the same error appear but :Must declare the scalar variable "@p_result"
not Must declare the scalar variable "@p_version"

10xxxxxxxxxxxxx man

[Frinavale]

Hi there!

I've moved this thread into the SQL Server forum.

Good Luck!

-Frinny

[rob313]

The problem is that you have a variable in the dynamic SQL, so the optimizer receives variables but they are not defined to the optimizer, only in you procedure. Change your procedure as follows where you end quote before a variable, then concatenate the variable and then start a new begin quote. I only did the first three variables here, so you will have to do the rest. A good debug technique would be to put a PRINT @p_build statement before your execute so you can see what the dynamic SQL is that you are really trying to execute. There should be no variable names in the final SQL, just the substituted values.

SET @p_build = 'SELECT sr_id AS ID,
sr_version AS Version,
sm_name AS SalesMan_Names,
CASE sr_result WHEN 1 THEN ''OK'' ELSE ''KO'' END AS Result,
sr_comments AS Comments,
sr_datetime AS Date
FROM SYNC_RESULT ,SALESMAN
WHERE sm_id = sr_sm_id and
((' + @p_version + ' IS NOT NULL AND sr_version = '+ @p_version +') OR (' + @p_version + ' IS NULL))