How to give where conditon for this query sql

Question

0.00/5 (No votes)

See more:

Dear Frnds,

I want to give "Where" condition in this query were i give this condition i don't know. please help me.

SQL

DECLARE @cols AS NVARCHAR(MAX),
@colsName AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(ItemCode +'_'+c.col)
from Vw_InOutWard
cross apply
( select 'TotalCount' col
union all
select 'TotalAmount') c
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')
select @colsName = STUFF((SELECT distinct ', ' + QUOTENAME(ItemCode +'_'+c.col) +' as [' + ItemCode + case when c.col = 'TotalCount' then ']' else ' Rate]' end
from Vw_InOutWard
cross apply
(select 'TotalCount' col
union all
select 'TotalAmount') c
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
set @query = 'SELECT DCNo,DCDate,FromSupName,FromAddress,FromContNo,ToSupName,ToAddress,ToContNo,RefferedBy, ' + @colsName + '
from
(select DCDate, DCNo,FromSupName,FromAddress,FromContNo,ToSupName,ToContNo,RefferedBy,ToAddress, ItemCode +''_''+col col, value
from
(select DCDate, ItemCode, DCNo,FromSupName,FromAddress,FromContNo,ToSupName,ToAddress,ToContNo,RefferedBy,
cast(Qty as numeric(10, 2)) totalcount,
cast(Rate as numeric(10, 2)) totalamount
from Vw_InOutWard) src
unpivot
(value for col in (totalcount, totalamount)) unpiv ) s
pivot (sum(value) for col in (' + @cols + ')) p '
execute(@query)

Posted 11-Oct-15 18:06pm

Vivek.anand34

Updated 11-Oct-15 19:57pm

Maciej Los

v3

Add a Solution

Comments

Maciej Los 12-Oct-15 1:58am

Somewhere between ''...

Vivek.anand34 12-Oct-15 2:43am

actually here dc no field is there.. i need to retrieve Where DCNo='DC-001'
or give parameter @dcno then how to call this paramter in asp .net c# code i.e, textbox value.

1 solution

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)

Abdul Samad KP · Accepted Answer · 2015-10-11T20:59:00

Solution 1

Pass DC No as a parameter from frontend and change set @query as below

SQL

set @query = 'DECLARE @DCNo INT;
SET @DCNo ='+Cast(@DCNO as VARCHAR)+';
SELECT DCNo,DCDate,FromSupName,FromAddress,FromContNo,ToSupName,ToAddress,ToContNo,RefferedBy, ' + @colsName + '
from
(select DCDate, DCNo,FromSupName,FromAddress,FromContNo,ToSupName,ToContNo,RefferedBy,ToAddress, ItemCode +''_''+col col, value
from
(select DCDate, ItemCode, DCNo,FromSupName,FromAddress,FromContNo,ToSupName,ToAddress,ToContNo,RefferedBy,
cast(Qty as numeric(10, 2)) totalcount,
cast(Rate as numeric(10, 2)) totalamount
from Vw_InOutWard WHERE DCNo=@DCNO ) src
UNPIVOT
(value for col in (totalcount, totalamount)) unpiv ) s
pivot (sum(value) for col in (' + @cols + ')) p '

Posted 11-Oct-15 20:59pm

Abdul Samad KP

Comments

Vivek.anand34 12-Oct-15 3:38am

ya i put tat line.. but underlined the @DCNO ' must declare the scalar variable @DCNO '

Abdul Samad KP 12-Oct-15 4:22am

You need to pass the @DCNo from frontend, ie your textbox value, and if it is not a integer then change set @query = 'DECLARE @DCNo INT;
to set @query = 'DECLARE @DCNo varchar;

By the way I assume , you put all this query in a stored procedure

Vivek.anand34 12-Oct-15 5:56am

Iput this line in front end:
cmd.Parameters.AddWithValue("@DcNo", txtDCNo.Text);

object reference not set an instance of an object is displayed

Abdul Samad KP 12-Oct-15 7:45am

Pls post full code

Vivek.anand34 12-Oct-15 8:19am

cmd = new SqlCommand("TEST", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@dc", txtDCNo.Text);
cmd.Connection = con;
cmd.ExecuteNonQuery();

Vivek.anand34 12-Oct-15 8:21am

DECLARE @cols AS NVARCHAR(MAX),
@colsName AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@dc as nvarchar(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(ItemCode +'_'+c.col)
from Vw_InOutWard
cross apply (
select 'TotalCount' col union all select 'TotalAmount') c
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')
select @colsName = STUFF((SELECT distinct ', ' + QUOTENAME(ItemCode +'_'+c.col) +' as [' + ItemCode + case when c.col = 'TotalCount' then ']' else ' Rate]' end
from Vw_InOutWard
cross apply
(select 'TotalCount' col union all select 'TotalAmount') c
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
set @query = 'SET @DCNo ='+Cast(@dc as VARCHAR)+'; SELECT DCNo,DCDate,FromSupName,FromAddress,FromContNo,ToSupName,ToAddress,ToContNo,RefferedBy, ' + @colsName + ' from (select DCDate, DCNo,FromSupName,FromAddress,FromContNo,ToSupName,ToContNo,RefferedBy,ToAddress, ItemCode +''_''+col col, value from(select DCDate, ItemCode, DCNo,FromSupName,FromAddress,FromContNo,ToSupName,ToAddress,ToContNo,RefferedBy,cast(Qty as numeric(10, 2)) totalcount,cast(Rate as numeric(10, 2)) totalamount
from Vw_InOutWard where DCNo=@dc) src
unpivot(value for col in (totalcount, totalamount)) unpiv ) s pivot
(sum(value) for col in (' + @cols + ')) p '
execute(@query)

Vivek.anand34 12-Oct-15 8:30am

actually in where condition i put 2 methods:
1. DCNO=@dc then "Must declare the scalar variable "@dc"."
2. if i put DCNo=' + @dc+ ' then "Invalid Column name error shown"

Abdul Samad KP 12-Oct-15 8:58am

remove 'SET @DCNo ='+Cast(@dc as VARCHAR)+';
and change
from Vw_InOutWard where DCNo=@dc) src

to

from Vw_InOutWard where DCNo=''' + @dc + ''') src

Beware of SQL injection, sanitise your textbox text before passing to the SP

Vivek.anand34 12-Oct-15 9:19am

Thank You its working now....