470,573 Members | 1,582 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,573 developers. It's quick & easy.

error in stored procedure

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




Expand|Select|Wrap|Line Numbers
  1. ALTER PROCEDURE [dbo].[GridView_Sort] (
  2.         @p_version nvarchar(100) = null,                               @p_result nvarchar(100) = null,
  3.         @Salesman int = null ,                               @from_Date datetime = null ,
  4.         @to_date datetime = null,
  5.         @order_by nvarchar(100) = null)
  6.  
  7.  
  8.  
  9. AS
  10. BEGIN
  11. DECLARE @p_build nvarchar(max);
  12.  
  13. if @order_by is null
  14. SET @order_by = ' order BY sr_id';
  15.  
  16.  
  17. SET @p_build = 'SELECT  sr_id AS ID,
  18. sr_version AS Version,
  19. sm_name AS SalesMan_Names,
  20. CASE sr_result WHEN 1 THEN ''OK'' ELSE ''KO'' END  AS Result,
  21. sr_comments AS Comments,
  22. sr_datetime  AS Date 
  23. FROM  SYNC_RESULT ,SALESMAN 
  24. WHERE sm_id = sr_sm_id and
  25.  ((@p_version IS NOT NULL AND sr_version = @p_version) OR (@p_version IS NULL)) 
  26. and ((@p_result IS NOT NULL AND sr_result = @p_result) OR (@p_result IS NULL))
  27.  and ((@Salesman IS NOT NULL AND sm_id = @Salesman) OR (@Salesman IS NULL))
  28.  and ((@from_Date IS NOT NULL AND CONVERT(nvarchar(32),sr_datetime, 112) >= CONVERT(nvarchar(32),@from_Date, 112))  OR (@from_Date IS NULL)) 
  29. 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 
  30.  
  31. EXEC sp_executesql @p_build ;
  32.  
  33.  
  34. END
Sep 20 '07 #1
6 2302
Plater
7,872 Expert 4TB
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
Expand|Select|Wrap|Line Numbers
  1. ALTER PROCEDURE [dbo].[GridView_Sort] (
  2. @p_version nvarchar(100) = null, 
  3. @p_result nvarchar(100) = null,
  4. @Salesman int = null , @from_Date datetime = null ,
  5. @to_date datetime = null,
  6. @order_by nvarchar(100) = null)
  7.  
Sep 20 '07 #2
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
Expand|Select|Wrap|Line Numbers
  1. ALTER PROCEDURE [dbo].[GridView_Sort] (
  2. @p_version nvarchar(100) = null, 
  3. @p_result nvarchar(100) = null,
  4. @Salesman int = null , @from_Date datetime = null ,
  5. @to_date datetime = null,
  6. @order_by nvarchar(100) = null)
  7.  


how can assin this variables??????????????/
Sep 20 '07 #3
Plater
7,872 Expert 4TB
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?
Sep 20 '07 #4
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
Sep 20 '07 #5
Frinavale
9,735 Expert Mod 8TB
Hi there!

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

Good Luck!

-Frinny
Sep 20 '07 #6
rob313
16
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))
Sep 20 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

9 posts views Thread by dtwilliams | last post: by
1 post views Thread by Jeremy Ames | last post: by
1 post views Thread by livre | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.