Error creating and executing stored procedure

Question

Error creating and executing stored procedure

Navigation

#1 by (1 votes)

0

I am trying to create a stored procedure which verifies all the projects in my database and gives me for each project a series of elements that I need.

When trying to do this, the procedure bugs me with an error Must declare the scalar variable "@ProjTitle".

I already declare the variable, but I do not know why this error is busting me.

Here is my code.

Greetings

SET NOCOUNT ON;
DECLARE @ProjID VARCHAR(100) -- ProjectID
DECLARE @ProjTitle VARCHAR(256) -- Project Title
DECLARE @ProjCat VARCHAR(256) -- Project Category
DECLARE @ProjSubCat VARCHAR(20) -- Project Subcategory
DECLARE @TasksTable nvarchar(256)
DECLARE @TaskDetailsTable nvarchar(256)
DECLARE @sql nvarchar(max)

DECLARE GetTasks CURSOR FOR  
SELECT Project.ProjectID, ProjectVersion.Title , CV1.CategoryName as Category, CV2.CategoryName as SubCategory
From Project LEFT JOIN ProjectVersion ON Project.ProjectID = ProjectVersion.ProjectID LEFT JOIN CategoryVersion AS CV1 ON Project.CategoryID = CV1.CategoryID AND
ProjectVersion.VersionLanguage = CV1.VersionLanguage
LEFT JOIN CategoryVersion AS CV2 ON Project.SubcategoryID = CV2.CategoryID AND
ProjectVersion.VersionLanguage = CV2.VersionLanguage

OPEN GetTasks
FETCH NEXT FROM GetTasks INTO @ProjID, @ProjTitle, @ProjCat, @ProjSubCat

WHILE @@FETCH_STATUS = 0  
BEGIN
  set @TasksTable =  '['+@ProjID+'Tasks]'
  set @TaskDetailsTable =  '['+@ProjID+'TaskDetails]'

  set @sql = 'SELECT Distinct @ProjTitle, @ProjCat, @ProjSubCat, TT.StartDate
        FROM Project, '+ @TasksTable+' as TT where Project.ProjectID = @ProjID
        and TT.ProjectID = Project.ProjectID'
  EXECUTE sp_executesql @sql
END

CLOSE GetTasks
DEALLOCATE GetTasks

sql sql-server query stored-procedure

asked by A arancibia 24.04.2017 в 21:34

source

1 answer

I need to update (UPDATE) the result of a select in mysql for node js and myadminphp does not accept the instruction help txt files in python

score 1 · Answer 1

I would recommend that you see well the need to use dynamic SQL and cursors. But going straight to your code, the problem is that the variables are using inside of the dynamic code, and your variables are valid outside it.

Fortunately, sp_executesql can be used thinking in this way, you just have to replace your line with the following one:

EXECUTE sp_executesql 
              @sql, 
              N'@ProjTitle VARCHAR(256), @ProjCat VARCHAR(256), @ProjSubCat VARCHAR(20), @ProjID VARCHAR(100)', 
              @ProjTitle, 
              @ProjCat, 
              @ProjSubCat,
              @ProjID
;