HOW TO USE SAME SP ,WITH WHERE CLAUSE AND ALSO WITHOUT WHERE

MANEESH AN

I HAVE TO DISPLAY CERTAIN COLUMNS IN GRID,WHEN I SELECT A 'SERIES' IN TEXTBOX.I HAVE THREE SERIES MAIN,SUB,ALL.MAINLY I WANT TO GET DISPLAY IN GRID'ALL' ONLY INCLUDING MAIN,SUB ALSO....I HAVE ONLY ONE SP WITH WHERE CONDITION FOR MAIN,SUB.....BECAUSE WHEN FORM LOAD..'ALL' WILL DISPLAY IN THAT TEXTBOX.NO NEED TO INSERT SERIES 'ALL',THEN CLICK BUTTON,GRID WILL DISPLAY.....WHAT CONDITION SHOULD APPL(IF...ELSE)Y? WHERE IT ON .NET OR SP....?HOW TO WRITE CODING?

11 Replies

Know the answer? Post it — somebody with the same question will find it here.

Pankaj Pandey13y ago
try now..

CREATE PROCEDURE [dbo].[sp_PaymentRegisterGrid]
(
@WhereCondition nvarchar(4000)='',
@vSeries nvarchar(20)=''

)

AS
begin
declare @sqlQuery nvarchar(4000);
set @sqlQuery='
SELECT
ACC.dRPDate DATE,
ACC.cVType TYPE,
TS.vSeries SERIES,

ACC.iRPNo VOUCHERNO,
ACM.vAcName HEAD,
ACC.vNarration NARRATION,
ACC.deDRAmount DEBIT,
ACC.deCRAmount CREDIT

FROM tbl_Acc_Voucher AS ACC INNER JOIN
tblSerialNumbers AS TS ON ACC.iSeriesId = TS.iSeriesId INNER JOIN
tbl_Acc_AccountMaster AS ACM ON ACM.iAcID = ACC.iAcID'

+@WhereCondition +' and (TS.vSeries='+@vSeries)

exec sp_executesql @sqlQuery
END
MANEESH AN13y ago
I SHOULD GET DISPLAY BOTH @WhereCondition nvarchar(4000), DATA
AND ALSO @vSeries nvarchar(20) ..NOW AT THIS TIME GETTING ONLY @WhereCondition DATA........IN SQL 2005...PLS GET ME ANSWER
Pankaj Pandey13y ago
hii

can you give detail that what is stored in both parameters at time of call stored procedure.

debug your code and copy-paste here that data stored in both variable then may be we can understand what the issue come here.
MANEESH AN13y ago
dt = objViewBL.fnPayRegDateView(vSeries, strWhereCondition);
DATATABLE BECAME EMPTY...WHY?I DONT GET
Riyaz Akhtar13y ago
try this

CREATE PROCEDURE [dbo].[sp_PaymentRegisterGrid]
(
@vSeries nvarchar(20)
)
AS
begin

IF(@vSeries='All')
set @vSeries=''

SELECT
ACC.dRPDate DATE,
ACC.cVType TYPE,
TS.vSeries SERIES,

ACC.iRPNo VOUCHERNO,
ACM.vAcName HEAD,
ACC.vNarration NARRATION,
ACC.deDRAmount DEBIT,
ACC.deCRAmount CREDIT

FROM tbl_Acc_Voucher AS ACC INNER JOIN
tblSerialNumbers AS TS ON ACC.iSeriesId = TS.iSeriesId INNER JOIN
tbl_Acc_AccountMaster AS ACM ON ACM.iAcID = ACC.iAcID

WHERE (@vSeries IS NULL OR @vSeries = '' OR TS.vSeries= @vSeries)
END
Pankaj Pandey13y ago
try it please

CREATE PROCEDURE [dbo].[sp_PaymentRegisterGrid]
(
@WhereCondition nvarchar(4000)='',
@vSeries nvarchar(20)=''

)

AS
begin
declare @sqlQuery nvarchar(4000);
set @sqlQuery='
SELECT
ACC.dRPDate DATE,
ACC.cVType TYPE,
TS.vSeries SERIES,

ACC.iRPNo VOUCHERNO,
ACM.vAcName HEAD,
ACC.vNarration NARRATION,
ACC.deDRAmount DEBIT,
ACC.deCRAmount CREDIT

FROM tbl_Acc_Voucher AS ACC INNER JOIN
tblSerialNumbers AS TS ON ACC.iSeriesId = TS.iSeriesId INNER JOIN
tbl_Acc_AccountMaster AS ACM ON ACM.iAcID = ACC.iAcID'

+@WhereCondition +' and TS.vSeries='+@vSeries

exec sp_executesql @sqlQuery
END

run it now
Iftikar Hussain13y ago
Hi,
Try like this

CREATE PROCEDURE [dbo].[sp_PaymentRegisterGrid]
(
@WhereCondition nvarchar(4000),
@vSeries nvarchar(20)

)

AS
begin
declare @sqlQuery nvarchar(4000);
set @sqlQuery='
SELECT
ACC.dRPDate DATE,
ACC.cVType TYPE,
TS.vSeries SERIES,

ACC.iRPNo VOUCHERNO,
ACM.vAcName HEAD,
ACC.vNarration NARRATION,
ACC.deDRAmount DEBIT,
ACC.deCRAmount CREDIT

FROM tbl_Acc_Voucher AS ACC INNER JOIN
tblSerialNumbers AS TS ON ACC.iSeriesId = TS.iSeriesId INNER JOIN
tbl_Acc_AccountMaster AS ACM ON ACM.iAcID = ACC.iAcID
WHERE (@vSeries IS NULL OR @vSeries='' OR TS.vSeries=@vSeries)
end

Regards,
Iftikar
MANEESH AN13y ago
CREATE PROCEDURE [dbo].[sp_PaymentRegisterGrid]
(
@WhereCondition nvarchar(4000),
@vSeries nvarchar(20)

)

AS
begin
declare @sqlQuery nvarchar(4000);
set @sqlQuery='
SELECT
ACC.dRPDate DATE,
ACC.cVType TYPE,
TS.vSeries SERIES,

ACC.iRPNo VOUCHERNO,
ACM.vAcName HEAD,
ACC.vNarration NARRATION,
ACC.deDRAmount DEBIT,
ACC.deCRAmount CREDIT

FROM tbl_Acc_Voucher AS ACC INNER JOIN
tblSerialNumbers AS TS ON ACC.iSeriesId = TS.iSeriesId INNER JOIN
tbl_Acc_AccountMaster AS ACM ON ACM.iAcID = ACC.iAcID'



+@WhereCondition


exec sp_executesql @sqlQuery
print @sqlQuery
END

--exec @sqlQuery

exec @sqlQuery WHERE TS.vSeries=@vSeries

HOW TO CORRECT THIS ?
MANEESH AN13y ago
CREATE PROCEDURE [dbo].[sp_PaymentRegisterGrid]
(
@WhereCondition nvarchar(4000),
@vSeries nvarchar(20)

)

AS
begin
declare @sqlQuery nvarchar(4000);
set @sqlQuery='
SELECT
ACC.dRPDate DATE,
ACC.cVType TYPE,
TS.vSeries SERIES,

ACC.iRPNo VOUCHERNO,
ACM.vAcName HEAD,
ACC.vNarration NARRATION,
ACC.deDRAmount DEBIT,
ACC.deCRAmount CREDIT

FROM tbl_Acc_Voucher AS ACC INNER JOIN
tblSerialNumbers AS TS ON ACC.iSeriesId = TS.iSeriesId INNER JOIN
tbl_Acc_AccountMaster AS ACM ON ACM.iAcID = ACC.iAcID'



+@WhereCondition


exec sp_executesql @sqlQuery
print @sqlQuery
END

--exec @sqlQuery

exec @sqlQuery WHERE TS.vSeries=@vSeries

HOW TO CORRECT IT?
Riyaz Akhtar13y ago
try below sql script in ur SP, if series is selected 'ALL' pass '' (empty) then all record will be displayed.

declare @sr varchar(100)
set @sr=''
select * from tablename where (@sr IS NULL OR @sr = '' OR SERIES= @sr)
Pankaj Pandey13y ago
try this..

create procedure abccd
@where nvarchar(max)=''
as
begin
declare @query nvarchar(max)
set @query='select * from tbl_Employee ' +@where
exec sp_executesql @query
print @query
end

now you can run this in both condition

exec abccd

or

exec abccd 'where empid=1'