T-SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

T-SQL

Author

Topic

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-09-14 : 20:44:14

Hi All,


CREATE PROCEDURE [dbo].sp_Get_LodgmentJobByDate (
@strClientList AS VARCHAR(1000) = '', 
)
AS

DECLARE @strCondition AS VARCHAR(3000)
DECLARE @strQuery AS VARCHAR(3000)
SET @strCondition = ''

IF LEN(RTRIM(LTRIM(@strClientList))) > 0
	SET @strCondition = @strCondition + 'Job.JobClientCode IN (' + @strClientList + ') '

SET @strQuery = 

'SELECT ......'

IF LEN(LTRIM(RTRIM(@strCondition))) > 0
	SET @strQuery = @strQuery + 'WHERE ' + @strCondition
EXEC(@strQuery)

Can i do same without dynamic sql.
Thanks

mk_garg

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-09-14 : 21:08:02

Give this a shot:

http://www.sqlteam.com/item.asp?ItemID=2652

Once parsed into a table, you can simply join to it in a normal query.

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-09-14 : 21:15:04

HI,
Sorry for confusing things.
I mean if @strClientList is not blank then use "where" to filter records otherwise dont use "where"

Thanks

mk_garg

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-09-14 : 22:04:15

[code]CREATE PROCEDURE dbo].sp_Get_LodgmentJobByDate (@strClientList AS VARCHAR(1000) = '') AS
SET NOCOUNT ON
IF LTRIM(@strClientList)=''
SELECT * FROM Job
ELSE
BEGIN
CREATE TABLE #a (code int not null)
SET @strClientList=',' + @strClientList + ','
INSERT #a(code)
SELECT cast(substring(@strClientList, ID, charindex(',', @strClientList, ID) - ID) as int) FROM Tally
WHERE ID < Len(@strClientList) AND SubString(@strClientList, ID - 1, 1) = ','
SELECT J.* FROM Job J INNER JOIN #a A ON J.JobClientCode=A.code
DROP TABLE #a
END[/code]That ought to do it (haven't tested it, let me know if there's problems)

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-09-14 : 22:38:20

Here is complete SP.


CREATE PROCEDURE [dbo].sp_Get_LodgmentJobByDate (@StartDate AS VARCHAR(10) = '', 
@EndDate AS VARCHAR(10) = '', 
@strClientList AS VARCHAR(1000) = '', 
@strJobList AS VARCHAR(1000) = '',
@FromRun AS VARCHAR(10) = '',
@ToRun AS VARCHAR(10) = '',
@InvoicedJobs AS INT = 0
)
AS

DECLARE @strQuery AS VARCHAR(8000)
DECLARE @strCondition AS VARCHAR(3000)
DECLARE @GST AS VARCHAR(3)

SET @GST = (SELECT SysGST from [CDS_Systems_Postal].._Sysdata WHERE SysRecNo = 1)

SET @strCondition = ''

IF LEN(RTRIM(LTRIM(@strClientList))) > 0
	SET @strCondition = @strCondition + 'Job.JobClientCode IN (' + @strClientList + ') '

IF LEN(LTRIM(RTRIM(@strJobList))) > 0
BEGIN
	IF LEN(LTRIM(RTRIM(@strCondition))) > 0
		SET @strCondition = @strCondition + 'AND '
	SET @strCondition = @strCondition + 'Lodgement.LdgJobNo IN (' + @strJobList + ') '
END

IF LEN(LTRIM(RTRIM(@StartDate))) > 0
BEGIN
	IF LEN(LTRIM(RTRIM(@strCondition))) > 0
		SET @strCondition = @strCondition + 'AND '
	SET @strCondition = @strCondition + 'Lodgement.LdgDate >= CONVERT( DATETIME, ''' + @StartDate + ''', 103) '
END

IF LEN(LTRIM(RTRIM(@EndDate))) > 0
BEGIN
	IF LEN(LTRIM(RTRIM(@strCondition))) > 0
		SET @strCondition = @strCondition + 'AND '
	SET @strCondition = @strCondition + 'Lodgement.LdgDate < CONVERT( DATETIME, ''' + @EndDate + ''', 103) + 1 '
END

IF LEN(LTRIM(RTRIM(@FromRun))) > 0
BEGIN
	IF LEN(LTRIM(RTRIM(@strCondition))) > 0
		SET @strCondition = @strCondition + 'AND '
	SET @strCondition = @strCondition + 'Lodgement.LdgJobRun >= ' + @FromRun
END

IF LEN(LTRIM(RTRIM(@ToRun))) > 0
BEGIN
	IF LEN(LTRIM(RTRIM(@strCondition))) > 0
		SET @strCondition = @strCondition + 'AND '
	SET @strCondition = @strCondition + 'Lodgement.LdgJobRun <= ' + @ToRun
END

IF @InvoicedJobs= 0
BEGIN
	IF LEN(LTRIM(RTRIM(@strCondition))) > 0
		SET @strCondition = @strCondition + 'AND '
	SET @strCondition = @strCondition + '  Job.InvoiceNumber IS NULL '
END

SELECT 	DISTINCT Lodgement.LdgJobNo as JobNumber,
	Lodgement.LdgJobRun as RunNumber,
	Contract.ClientID AS JobClientCode,
	Contract.ContractName AS JobName,
	ContractRun.InvoiceDate,
	ContractRun.InvoiceNumber,
	'CDS Limited' AS JobRep,
	'C' AS Type INTO #Job
from [CDS_Systems_Postal]..Lodgement Lodgement
LEFT JOIN ContractRun ON (Lodgement.LdgJobNo=ContractRun.ContractId and Lodgement.LdgJobRun=ContractRun.RunNumber)
LEFT JOIN Contract ON Lodgement.LdgJobNo=Contract.ContractID
Where IsNumeric(Lodgement.LdgJobNo)=0 and Lodgement.LdgDate>=Convert(DateTime,@StartDate,103)
	and Lodgement.LdgDate<Convert(DateTime,@EndDate,103)+1 and Lodgement.LdgCancelled=0
UNION ALL

SELECT DISTINCT	Lodgement.LdgJobNo as JobNumber,
	Lodgement.LdgJobRun as RunNumber,
	Job.ClientID  AS JobClientCode,
	Job.JobName  AS JobName, 	
	JobInvoice.InvoiceDate,
	JobInvoice.InvoiceNo  as InvoiceNumber,
	'CDS Limited',
	'P'
from [CDS_Systems_Postal]..Lodgement Lodgement
LEFT JOIN JobInvoice ON Lodgement.LdgJobNo=JobInvoice.JobNumber
LEFT JOIN Job ON Lodgement.LdgJobNo=Job.JobNumber
Where IsNumeric(Lodgement.LdgJobNo)=1 and Lodgement.LdgDate>=Convert(DateTime,@StartDate,103)
	and Lodgement.LdgDate<Convert(DateTime,@EndDate,103)+1 and Lodgement.LdgCancelled=0
Order by Lodgement.LdgJobNo

SET @strQuery = 

'SELECT Job.JobClientCode, Company.CompanyName, Job.JobNumber, Job.JobName, Job.Type, Lodgement.*, 
LodgeType = (SELECT LDgDesc FROM [CDS_Systems_Postal]..LodgementTypes LodgementTypes WHERE LodgementTypes.LdgType = Lodgement.LdgType ),
ISNULL(Company.ClnRetainedPS, 0) AS ClnRetainedPS, ISNULL(Company.ClnRetainedPAR, 0) AS ClnRetainedPAR, ISNULL(Company.ClnRetainedOS, 0) AS ClnRetainedOS, Job.JobRep, 
LodgementCostexcl = (LdgTotalCost - LdgTotGST), AmdDiscEXCL = LdgAMDDisc * 100 / (100 + ' + @GST + '),
FullRateEXCL = 
CASE Lodgement.LdgType 
	WHEN "OS" THEN ldgFullRate
	WHEN "MSC" THEN (ldgFullRate - LdgTotGST)
	WHEN "PP" THEN (ldgFullRate - LdgTotGST)
	WHEN "RP" THEN (ldgFullRate - LdgTotGST)
	WHEN "APD" THEN (ldgFullRate - LdgTotGST)
	WHEN "APO" THEN (ldgFullRate - LdgTotGST)
	WHEN "EP" THEN (ldgFullRate - LdgTotGST)
	WHEN "POP" THEN (ldgFullRate - LdgTotGST)
	WHEN "PAR" THEN (ldgFullRate * 100 / (100 + ' + @GST + ')) 
	WHEN "PSB" THEN (ldgFullRate * 100 / (100 + ' + @GST + '))
	WHEN "PSU" THEN (ldgFullRate * 100 / (100 + ' + @GST + '))
	ELSE (LdgFullRate * 100 / (100 + ' + @GST + '))
END, 
DiscountRetained = 
CASE Lodgement.LdgType
	WHEN "OS" THEN Company.ClnRetainedOS
	WHEN "PAR" THEN Company.ClnRetainedOS
	WHEN "PSB" THEN Company.ClnRetainedOS
	WHEN "PSU" THEN Company.ClnRetainedOS
	ELSE 0
END,
ClientDiscount = 
CASE Lodgement.LdgType
	WHEN "OS" THEN ((ldgFullRate - (LdgTotalCost - LdgTotGST))*(100 - ISNULL(ClnRetainedOS, 0))/100)
	WHEN "MSC" THEN ((ldgFullRate - LdgTotGST) - (LdgTotalCost - LdgTotGST))
	WHEN "PP" THEN ((ldgFullRate - LdgTotGST) - (LdgTotalCost - LdgTotGST))
	WHEN "RP" THEN ((ldgFullRate - LdgTotGST) - (LdgTotalCost - LdgTotGST))
	WHEN "APD" THEN ((ldgFullRate - LdgTotGST) - (LdgTotalCost - LdgTotGST))
	WHEN "APO" THEN ((ldgFullRate - LdgTotGST) - (LdgTotalCost - LdgTotGST))
	WHEN "EP" THEN ((ldgFullRate - LdgTotGST) - (LdgTotalCost - LdgTotGST))
	WHEN "POP" THEN ((ldgFullRate - LdgTotGST) - (LdgTotalCost - LdgTotGST))
	WHEN "PAR" THEN (((ldgFullRate * 100 / (100 + ' + @GST + ')) - (LdgTotalCost - LdgTotGST))*(100 - ISNULL(ClnRetainedPAR, 0))/100)
	WHEN "PSB" THEN (((ldgFullRate * 100 / (100 + ' + @GST + ')) - (LdgTotalCost - LdgTotGST + LdgAMDDisc))*(100 - ISNULL(ClnRetainedPS, 0))/100) 
	WHEN "PSU" THEN (((ldgFullRate * 100 / (100 + ' + @GST + ')) - (LdgTotalCost - LdgTotGST + LdgAMDDisc))*(100 - ISNULL(ClnRetainedPS, 0))/100)
	ELSE ((LdgFullRate * 100 / (100 + ' + @GST + ')) - (LdgTotalCost - LdgTotGST))

END,
Chargeable = 
CASE Lodgement.LdgType
	WHEN "OS" THEN (ldgFullRate - ((ldgFullRate - (LdgTotalCost - LdgTotGST))*(100 - ISNULL(ClnRetainedOS, 0))/100))
	WHEN "MSC" THEN ((ldgFullRate - LdgTotGST) - ((ldgFullRate - LdgTotGST) - (LdgTotalCost - LdgTotGST)))
	WHEN "PP" THEN ((ldgFullRate - LdgTotGST) - ((ldgFullRate - LdgTotGST) - (LdgTotalCost - LdgTotGST)))
	WHEN "RP" THEN ((ldgFullRate - LdgTotGST) - ((ldgFullRate - LdgTotGST) - (LdgTotalCost - LdgTotGST)))
	WHEN "APD" THEN ((ldgFullRate - LdgTotGST) - ((ldgFullRate - LdgTotGST) - (LdgTotalCost - LdgTotGST)))
	WHEN "APO" THEN ((ldgFullRate - LdgTotGST) - ((ldgFullRate - LdgTotGST) - (LdgTotalCost - LdgTotGST)))
	WHEN "EP" THEN ((ldgFullRate - LdgTotGST) - ((ldgFullRate - LdgTotGST) - (LdgTotalCost - LdgTotGST)))
	WHEN "POP" THEN ((ldgFullRate - LdgTotGST) - ((ldgFullRate - LdgTotGST) - (LdgTotalCost - LdgTotGST)))
	WHEN "PAR" THEN ((ldgFullRate * 100 / (100 + ' + @GST + ')) - (((ldgFullRate * 100 / (100 + ' + @GST + ')) - (LdgTotalCost - LdgTotGST))*(100 - ISNULL(ClnRetainedPAR, 0))/100))
	WHEN "PSB" THEN ((ldgFullRate * 100 / (100 + ' + @GST + ')) - (((ldgFullRate * 100 / (100 + ' + @GST + ')) - (LdgTotalCost - LdgTotGST + LdgAMDDisc))*(100 - ISNULL(ClnRetainedPS, 0))/100))
	WHEN "PSU" THEN ((ldgFullRate * 100 / (100 + ' + @GST + ')) - (((ldgFullRate * 100 / (100 + ' + @GST + ')) - (LdgTotalCost - LdgTotGST + LdgAMDDisc))*(100 - ISNULL(ClnRetainedPS, 0))/100))
	ELSE ((LdgFullRate * 100 / (100 + ' + @GST + ')) - ((LdgFullRate * 100 / (100 + ' + @GST + ')) - (LdgTotalCost - LdgTotGST)))
END, 
ChargeableGST = 
CASE Lodgement.LdgType
	WHEN "OS" THEN 0
	WHEN "MSC" THEN ((ldgFullRate - LdgTotGST) - ((ldgFullRate - LdgTotGST) - (LdgTotalCost - LdgTotGST))) / 100 * ' + @GST + '
	WHEN "PP" THEN ((ldgFullRate - LdgTotGST) - ((ldgFullRate - LdgTotGST) - (LdgTotalCost - LdgTotGST))) / 100 * ' + @GST + '
	WHEN "RP" THEN ((ldgFullRate - LdgTotGST) - ((ldgFullRate - LdgTotGST) - (LdgTotalCost - LdgTotGST))) / 100 * ' + @GST + '
	WHEN "APD" THEN ((ldgFullRate - LdgTotGST) - ((ldgFullRate - LdgTotGST) - (LdgTotalCost - LdgTotGST))) / 100 * ' + @GST + '
	WHEN "APO" THEN ((ldgFullRate - LdgTotGST) - ((ldgFullRate - LdgTotGST) - (LdgTotalCost - LdgTotGST))) / 100 * ' + @GST + '
	WHEN "EP" THEN ((ldgFullRate - LdgTotGST) - ((ldgFullRate - LdgTotGST) - (LdgTotalCost - LdgTotGST))) / 100 * ' + @GST + '
	WHEN "POP" THEN ((ldgFullRate - LdgTotGST) - ((ldgFullRate - LdgTotGST) - (LdgTotalCost - LdgTotGST))) / 100 * ' + @GST + '
	WHEN "PAR" THEN ((ldgFullRate * 100 / (100 + ' + @GST + ')) - (((ldgFullRate * 100 / (100 + ' + @GST + ')) - (LdgTotalCost - LdgTotGST))*(100 - ISNULL(ClnRetainedPAR, 0))/100)) / 100 * ' + @GST + '
	WHEN "PSB" THEN ((ldgFullRate * 100 / (100 + ' + @GST + ')) - (((ldgFullRate * 100 / (100 + ' + @GST + ')) - (LdgTotalCost - LdgTotGST + LdgAMDDisc))*(100 - ISNULL(ClnRetainedPS, 0))/100)) / 100 * ' + @GST + '
	WHEN "PSU" THEN ((ldgFullRate * 100 / (100 + ' + @GST + ')) - (((ldgFullRate * 100 / (100 + ' + @GST + ')) - (LdgTotalCost - LdgTotGST + LdgAMDDisc))*(100 - ISNULL(ClnRetainedPS, 0))/100)) / 100 * ' + @GST + '
	ELSE ((LdgFullRate * 100 / (100 + ' + @GST + ')) - ((LdgFullRate * 100 / (100 + ' + @GST + ')) - (LdgTotalCost - LdgTotGST))) / 100 * ' + @GST + '
END, 
Job.InvoiceNumber,Job.InvoiceDate
FROM [CDS_Systems_Postal]..Lodgement Lodgement LEFT JOIN #Job Job
ON (Lodgement.LdgJobNo = Job.JobNumber and Lodgement.LdgJobRun = Job.RunNumber)
LEFT JOIN Company on Job.JobClientCode = Company.CiId '

IF LEN(LTRIM(RTRIM(@strCondition))) > 0
	SET @strCondition = @strCondition + 'AND '
SET @strCondition = @strCondition + 'Lodgement.LdgCancelled = 0 '

IF LEN(LTRIM(RTRIM(@strCondition))) > 0
	SET @strQuery = @strQuery + 'WHERE ' + @strCondition
EXEC(@strQuery)

DROP TABLE #Job
GO

Should i use your code for all variables.

Thanks Rob

mk_garg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources