Sql-getting error must declare the scalar variable "@from_DATE".

Question

0.00/5 (No votes)

See more:

SQL

DECLARE @Columns NVARCHAR(MAX) = ''
DECLARE @FROM_DATE date
DECLARE @TO_DATE date 
set  @FROM_DATE = '10/11/2017' 
set @TO_DATE ='10/11/2018'
      SELECT @Columns  =
      @Columns + QUOTENAME(marriage_type) + ','
      FROM (SELECT top 4 marriage_type from marriages m,DOCUMENT_TYPES d
      where d.recordable_document='Y' and m.marriage_type=d.document_type and d.MODULE_ID = 'MARRIAGE'
      group by marriage_type) t
      SELECT @Columns = SUBSTRING(@Columns, 0, LEN(@Columns))
      DECLARE @Query NVARCHAR(MAX) =
      'SELECT location,ISNULL(Year(DATE_OF_APP),null) as Year ,  DATENAME(month, 2018/str(Month(DATE_OF_APP))/2) as MONTH,' + @Columns + ' FROM
      (SELECT DATE_OF_APP, location,marriage_type  FROM marriages m where DATE_OF_APP is not null and m.DATE_OF_APP >= @FROM_DATE AND m.DATE_OF_APP <= @TO_DATE) SourceTable
      PIVOT
      (
      count(marriage_type)
      FOR marriage_type IN (' + @Columns + ')
      ) AS PivotTable '
      EXECUTE(@query)

What I have tried:

SQL

DECLARE @Columns NVARCHAR(MAX) = ''
DECLARE @FROM_DATE date
DECLARE @TO_DATE date 
set  @FROM_DATE = '10/11/2017' 
set @TO_DATE ='10/11/2018'
      SELECT @Columns  =
      @Columns + QUOTENAME(marriage_type) + ','
      FROM (SELECT top 4 marriage_type from marriages m,DOCUMENT_TYPES d
      where d.recordable_document='Y' and m.marriage_type=d.document_type and d.MODULE_ID = 'MARRIAGE'
      group by marriage_type) t
      SELECT @Columns = SUBSTRING(@Columns, 0, LEN(@Columns))
      DECLARE @Query NVARCHAR(MAX) =
      'SELECT location,ISNULL(Year(DATE_OF_APP),null) as Year ,  DATENAME(month, 2018/str(Month(DATE_OF_APP))/2) as MONTH,' + @Columns + ' FROM
      (SELECT DATE_OF_APP, location,marriage_type  FROM marriages m where DATE_OF_APP is not null and m.DATE_OF_APP >= @FROM_DATE AND m.DATE_OF_APP <= @TO_DATE) SourceTable
      PIVOT
      (
      count(marriage_type)
      FOR marriage_type IN (' + @Columns + ')
      ) AS PivotTable '
      EXECUTE(@query)

I am getting error :

Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@FROM_DATE".

Posted 28-Nov-18 7:27am

Member 11149766

Updated 29-Nov-18 22:12pm

Blutfaust

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Answer 1 · 2018-11-28T08:29:00

Solution 2

To solve the error, use sp_executesql[^], passing in the parameters used by your query:

SQL

EXEC sp_executesql @Query, 
    N'@FROM_DATE date, @TO_DATE date', 
    @FROM_DATE = @FROM_DATE, 
    @TO_DATE = @TO_DATE;

Posted 28-Nov-18 8:29am

Richard Deeming

OriginalGriff · Answer 2 · 2018-11-28T07:44:00

@FROM_DATE and @TO_DATE are declared in your outer code that builds the query string, and referenced within the string when it is executed as a separate command.

Since the command doesn't know what they are you get the error.

There are other things:
1) Don't set dates in that format: use yyyy-MM-dd instead as it can;t be misinterpreted as either dd-MM-yyyy or MM-dd-yyyy depending on the settings of the SQL box.
2) I'd think very carefully before doing that sort of query: you are building a query that tries to be dynamic, but actually risks SQL Injection via the Columns data - particularly since you don't specify order the the columns should be returned in, so SQL is at liberty to return them in any order it prefers.
Personally, I wouldn't do that: it's a dangerous solution to a cacky problem.

kirthiga S · Answer 3 · 2018-11-29T22:13:00

Passing parameters inside the query will solve the issue

DECLARE @Columns NVARCHAR(MAX) = ''
SELECT @Columns  =
@Columns + QUOTENAME(marriage_type) + ','
FROM (SELECT top 4 marriage_type from marriages m,DOCUMENT_TYPES d
where d.recordable_document='Y' and m.marriage_type=d.document_type and d.MODULE_ID = 'MARRIAGE'
group by marriage_type) t
SELECT @Columns = SUBSTRING(@Columns, 0, LEN(@Columns))
DECLARE @Query NVARCHAR(MAX) =
'DECLARE @FROM_DATE date= ''10/11/2017''
DECLARE @TO_DATE date=''10/11/2018''
SELECT location,ISNULL(Year(DATE_OF_APP),null) as Year ,  DATENAME(month, 2018/str(Month(DATE_OF_APP))/2) as MONTH,' + @Columns + ' FROM
(SELECT DATE_OF_APP, location,marriage_type  FROM marriages m where DATE_OF_APP is not null and m.DATE_OF_APP >= @FROM_DATE AND m.DATE_OF_APP <= @TO_DATE) SourceTable
PIVOT
(
count(marriage_type)
FOR marriage_type IN (' + @Columns + ')
) AS PivotTable '
EXECUTE(@query)

Sql-getting error must declare the scalar variable "@from_DATE".

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0