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 2005 Forums
 Transact-SQL (2005)
 SQL Stored Procedure Problem

Author  Topic 

Mari
Starting Member

19 Posts
Posted - 2007-08-27 : 12:47:41
Dear everyone,

I've been getting the message 'Must Declare the variable @Role. 'Must Declare the variable @Role.'Must Declare the variable @Role.' when I have already declared it with a default value. So what is missing? and when I try to use the 'Declare' function locally(Declare @Role varchar(20), set @='1503') it says too many arguments.


CREATE PROCEDURE dbo.sp_Select_1503SmallCard
@Author varchar(500),
@AuthorType varchar(50),
@CountryAgainst varchar(3),
@IndexNumber bigint,
@year varchar(4),
@Search varchar(10),
@Summary varchar(100),
@PageSize INT =1,
@PageIndex INT,
@Role varchar(20)='1503'

AS

select @Author=REPLACE(@Author,' ','%')
declare @sql1 varchar(8000)
select @sql1= 'select CaseID,authortypeshortcode,t.authortypecode,[ID]=CaseID,CaseCode,
IndexNumber,ca.CountryAgainst,Summary,c.Comments,
LetterDateStart=dbo.FormatDate(LetterDateStart,''dd/mm/yyyy''),
LetterDateEnd=dbo.FormatDate(LetterDateEnd,''dd/mm/yyyy''),
LetterDateSource,LetterLanguage,SeenBy1,SeenBy2,SeenBy3,Cleared,
Sent_Date=dbo.FormatDate(Sent_Date,''dd/mm/yyyy''),Type_Ack,
s.SourceID,Author,FirstName='''',Lastname='''',Address,CountryID,
Head='''',OrgCity='''',OrgTel='''',OrgFax='''',RegistryDate=dbo.FormatDate(c.RegistryDate,''dd/mm/yyyy''),OrgAuthorType=''''
from
Complaint c, [1503_Case] ca, [1503_SourceDetails] s, [1503_AuthorType] t
where
c.complaintid=ca.complaintid and
c.complaintid=s.complaintid and sourceID=0 and s.authortypecode=t.authortypecode
and c.Role=@Role and ca.Role=@Role and s.Role=@Role and t.Role=@Role '

IF @CountryAgainst<>'0' select @sql1=@sql1 + ' and ca.CountryAgainst=''' + @CountryAgainst + ''''
IF @IndexNumber>0 select @sql1=@sql1 + 'and ca.IndexNumber=' + cast(@IndexNumber as varchar(1000) )
IF @IndexNumber=-1 select @sql1=@sql1 + 'and ca.IndexNumber=0'
IF @AuthorType<>'0' select @sql1=@sql1 + ' and s.AuthorTypeCode='''+ @AuthorType + ''''
IF @Author<>'0' select @sql1=@sql1 + ' and s.Author like ''%' + @Author + '%'''
IF @Year>0 select @sql1=@sql1 + ' and Year(c.LetterDateStart)=' + @Year
IF @Summary<>'0' select @sql1=@sql1 + ' and c.Summary Like ''%' + @Summary + '%'''

declare @sql2 varchar(8000)
select @sql2=' UNION ALL
select CaseID,authortypeshortcode,t.authortypecode,[ID]=CaseID,CaseCode, IndexNumber,ca.CountryAgainst,Summary,c.Comments,
LetterDateStart=dbo.FormatDate(LetterDateStart,''dd/mm/yyyy''),
LetterDateEnd=dbo.FormatDate(LetterDateEnd,''dd/mm/yyyy''),LetterDateSource,LetterLanguage,SeenBy1,SeenBy2,SeenBy3,Cleared,
Sent_Date=dbo.FormatDate(Sent_Date,''dd/mm/yyyy''),Type_ack,
s.SourceID,Author=FirstName + '', '' + LastName,FirstName,LastName,p.Address,p.CountryID,Head='''',OrgCity='''',OrgTel='''',OrgFax='''',
RegistryDate=dbo.FormatDate(c.RegistryDate,''dd/mm/yyyy''),OrgAuthorType=''''
from
Complaint c, [1503_Case] ca, [1503_SourceDetails] s,Person p, [1503_AuthorType] t
where
c.complaintid=ca.complaintid and
c.complaintid=s.complaintid and s.authortypecode=t.authortypecode and authortypeshortcode=''Ind'' and s.sourceid=p.personid
and c.Role=@Role and ca.Role=@Role and s.Role=@Role and p.Role=@Role, t.Role=@Role'


IF @CountryAgainst<>'0' select @sql2=@sql2 + 'and ca.CountryAgainst=''' + @CountryAgainst + ''''
IF @IndexNumber>0 select @sql2=@sql2 + 'and ca.IndexNumber=' + cast(@IndexNumber as varchar(1000) )
IF @IndexNumber=-1 select @sql2=@sql2 + 'and ca.IndexNumber=0'
IF @AuthorType<>'0' select @sql2=@sql2 + ' and s.AuthorTypeCode='''+ @AuthorType + ''''
IF @Author<>'0' select @sql2=@sql2 + ' and s.Author like ''%' + @Author + '%'''
IF @Year>0 select @sql2=@sql2 + ' and Year(c.LetterDateStart)=' + @Year
IF @Summary<>'0' select @sql2=@sql2 + ' and c.Summary Like ''%' + @Summary + '%'''

declare @sql3 varchar(8000)
select @sql3=' UNION ALL
select CaseID,authortypeshortcode,t.authortypecode,[ID]=CaseID,CaseCode, IndexNumber,ca.CountryAgainst,Summary,c.Comments,
LetterDateStart=dbo.FormatDate(LetterDateStart,''dd/mm/yyyy''),
LetterDateEnd=dbo.FormatDate(LetterDateEnd,''dd/mm/yyyy''),LetterDateSource,LetterLanguage,SeenBy1,SeenBy2,SeenBy3,Cleared,
Sent_Date=dbo.FormatDate(Sent_Date,''dd/mm/yyyy''),Type_Ack,
s.SourceID,Author=Author,FirstName=Author,LastName=OfficialName,p.Address,p.CountryID,p.Head,
OrgCity=p.City,OrgTel=p.Tel,OrgFax=p.Fax,RegistryDate=dbo.FormatDate(c.RegistryDate,''dd/mm/yyyy''),OrgAuthorType=p.Type
from
Complaint c, [1503_Case] ca, [1503_SourceDetails] s,[1503_Institution] p, [1503_AuthorType] t
where
c.complaintid=ca.complaintid and
c.complaintid=s.complaintid and s.sourceid=p.institutionid and s.authortypecode=t.authortypecode
and c.Role=@Role and ca.Role=@Role and s.Role=@Role and p.Role=@Role and t.Role=@Role '

IF @CountryAgainst<>'0' select @sql3=@sql3 + 'and ca.CountryAgainst=''' + @CountryAgainst + ''''
IF @IndexNumber>0 select @sql3=@sql3 + 'and ca.IndexNumber=' + cast(@IndexNumber as varchar(1000) )
IF @IndexNumber=-1 select @sql3=@sql3 + 'and ca.IndexNumber=0'
IF @AuthorType<>'0' select @sql3=@sql3 + ' and s.AuthorTypeCode='''+ @AuthorType + ''''
IF @Author<>'0' select @sql3=@sql3 + ' and s.Author like ''%' + @Author + '%'''
IF @Year>0 select @sql3=@sql3 + ' and Year(c.LetterDateStart)=' + @Year
IF @Summary<>'0' select @sql3=@sql3 + ' and c.Summary Like ''%' + @Summary + '%'''
DECLARE @SizeString AS VARCHAR(5)
DECLARE @PrevString AS VARCHAR(5)
SET @SizeString = CONVERT(VARCHAR, @PageSize)
SET @PrevString = CONVERT(VARCHAR, @PageSize * (@PageIndex - 1))
declare @Table varchar(10)
SET @Table='#temp'
declare @sql varchar(8000)
select @sql='
select CaseID , authortypeshortcode ,authortypecode ,IndexNumber,
[ID], CaseCode ,CountryAgainst ,Summary , Comments ,LetterDateStart ,
LetterDateEnd ,LetterDateSource ,LetterLanguage , SeenBy1 ,
SeenBy2 ,SeenBy3 ,Cleared ,Sent_Date ,Type_Ack ,
sourceID ,Author ,FirstName ,LastName ,Address ,
CountryID ,Head,OrgCity ,OrgTel ,OrgFax ,RegistryDate,
OrgAuthorType
into #temp from ( ' + @sql1 + @sql2 + @sql3 + ') xx '
+ CHAR(13)
+ 'SELECT * FROM ' + @Table + ' cc
WHERE CaseID IN
(SELECT TOP ' + @SizeString + ' CaseID FROM ' + @Table + ' cc1
WHERE CaseID NOT IN
(SELECT TOP ' + @PrevString + ' CaseID FROM
' + @Table + ' cc2
ORDER BY CaseID)
ORDER BY CaseID)
ORDER BY CaseID'


exec (@sql)
GO

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2007-08-27 : 12:51:48
what does this have to do with .Net inside SQL??
moved to proper forum

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2007-08-27 : 12:53:55
you're getting the error becaue you are using the variable inside the dynamic select which is parsed in a different scope where the variable isn't available.
change:
and c.Role=@Role and ca.Role=@Role and s.Role=@Role and t.Role=@Role
to
and c.Role=' + @Role + ' and ca.Role=' + @Role + ' and s.Role=' + @Role + ' and t.Role=' + @Role + '
do this everywhere else where you're doing the same thing.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts
Posted - 2007-08-27 : 12:57:59
you cannot use a variable declared outside DSQL inside DSQL. They are out of scope. You need to concatenate them.
somethig like this:


and c.Role=''' + @Role + ''' and ca.Role=''' + @Role + ''' and s.Role=''' + @Role + ''' and t.Role=''' + @Role +  ''''


Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

Mari
Starting Member

19 Posts
Posted - 2007-08-27 : 13:14:59
Thanks, guys,it's working now!

Sorry about putting it into the wrong forum, my frist time to join :-)

Thanks again!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-08-27 : 14:38:31
Drop the DYNAMIC SQL altogether!
CREATE PROCEDURE dbo.sp_Select_1503SmallCard
(
	@Author VARCHAR(500),
	@AuthorType VARCHAR(50),
	@CountryAgainst VARCHAR(3),
	@IndexNumber BIGINT,
	@Year VARCHAR(4),
	@Search VARCHAR(10),
	@Summary VARCHAR(100),
	@PageSize INT = 1,
	@PageIndex INT,
	@Role VARCHAR(20) = '1503' 
)
AS

SET NOCOUNT ON

SET	@Author = REPLACE(@Author, ' ', '%')

SELECT		CaseID,
		AuthorTypeShortCode,
		AuthorTypeCode,
		[ID],
		CaseCode,
		IndexNumber,
		CountryAgainst,
		Summary,
		Comments,
		LetterDateStart ,
		LetterDateEnd,
		LetterDateSource,
		LetterLanguage,
		SeenBy1,
		SeenBy2,
		SeenBy3,
		Cleared,
		Sent_Date,
		Type_Ack,
		SourceID,
		Author,
		FirstName,
		LastName,
		Address,
		CountryID,
		Head,
		OrgCity,
		OrgTel,
		OrgFax,
		RegistryDate,
		OrgAuthorType
FROM		(
			SELECT	CaseID,
				AuthorTypeShortCode,
				AuthorTypeCode,
				[ID],
				CaseCode,
				IndexNumber,
				CountryAgainst,
				Summary,
				Comments,
				LetterDateStart ,
				LetterDateEnd,
				LetterDateSource,
				LetterLanguage,
				SeenBy1,
				SeenBy2,
				SeenBy3,
				Cleared,
				Sent_Date,
				Type_Ack,
				SourceID,
				Author,
				FirstName,
				LastName,
				Address,
				CountryID,
				Head,
				OrgCity,
				OrgTel,
				OrgFax,
				RegistryDate,
				OrgAuthorType,
				ROW_NUMBER() OVER (ORDER BY CaseID) AS RecID
			FROM	(
					SELECT		CaseID,
							AuthorTypeShortCode,
							t.AuthorTypeCode,
							CaseID AS [ID],
							CaseCode,
							IndexNumber,
							ca.CountryAgainst,
							Summary,
							c.Comments,
							CONVERT(CHAR(10), LetterDateStart, 103) AS LetterDateStart ,
							CONVERT(CHAR(10), LetterDateEnd, 103) AS LetterDateEnd,
							LetterDateSource,
							LetterLanguage,
							SeenBy1,
							SeenBy2,
							SeenBy3,
							Cleared,
							CONVERT(CHAR(10), Sent_Date, 103) AS Sent_Date,
							Type_Ack,
							s.SourceID,
							Author,
							'' AS FirstName,
							'' AS LastName,
							Address,
							CountryID,
							'' AS Head,
							'' AS OrgCity,
							'' AS OrgTel,
							'' AS OrgFax,
							CONVERT(CHAR(10), c.RegistryDate, 103) AS RegistryDate,
							'' AS OrgAuthorType
					FROM		Complaint AS c
					INNER JOIN	[1503_Case] AS ca ON ca.ComplaintID = c.ComplaintID
					INNER JOIN	[1503_SourceDetails] AS s ON s.ComplaintID =c.ComplaintID
					INNER JOIN	[1503_AuthorType] AS t ON t.AuthorTypeCode = s.AuthorTypeCode
					WHERE		SourceID = 0
							AND c.Role = @Role
							AND ca.Role = @Role
							AND s.Role = @Role
							AND t.Role = @Role
							AND ca.IndexNumber =	CASE
											WHEN @IndexNumber > 0 THEN @IndexNumber
											ELSE 0
										END
							AND ca.CountryAgainst =	CASE
											WHEN @CountryAgainst = '0' THEN ca.CountryAgainst
											ELSE @CountryAgainst
										END
							AND ca.AuthorTypeCode =	CASE
											WHEN @AuthorType = '0' THEN ca.AuthorTypeCode
											ELSE @AuthorType
										END
							AND ca.Author LIKE	CASE
											WHEN @Author = '0' THEN ca.Author
											ELSE '%' + @Author + '%'
										END
							AND Year(c.LetterDateStart) =	CASE
												WHEN @Year > 0 THEN @Year
												ELSE Year(c.LetterDateStart)
											END
							AND ca.Summary LIKE	CASE
											WHEN @Summary = '0' THEN ca.Summary
											ELSE '%' + @Summary + '%'
										END

					UNION ALL

					SELECT		CaseID,
							AuthorTypeShortCode,
							t.AuthorTypeCode,
							CaseID AS [ID],
							CaseCode,
							IndexNumber
							ca.CountryAgainst,
							Summary,
							c.Comments,
							CONVERT(CHAR(10), LetterDateStart, 103) AS LetterDateStart,
							CONVERT(CHAR(10), LetterDateEnd, 103) AS LetterDateEnd,
							LetterDateSource,
							LetterLanguage,
							SeenBy1,
							SeenBy2,
							SeenBy3,
							Cleared,
							CONVERT(CHAR(10), Sent_Date, 103) AS Sent_Date,
							Type_Ack,
							s.SourceID,
							FirstName + ', ' + LastName AS Author,
							FirstName,
							LastName,
							p.Address,
							p.CountryID,
							'' AS Head,
							'' AS OrgCity,
							'' AS OrgTel,
							'' AS OrgFax,
							CONVERT(CHAR(10), c.RegistryDate, 103) AS RegistryDate,
							'' AS OrgAuthorType
					FROM		Complaint AS c
					INNER JOIN	[1503_Case] AS ca ON ca.ComplaintID = c.ComplaintID
					INNER JOIN	[1503_SourceDetails] AS s ON s.ComplaintID = c.ComplaintID
					INNER JOIN	[1503_AuthorType] AS t ON t.AuthorTypeCode = s.AuthorTypeCode
					INNER JOIN	Person AS p ON p.PersonID = s.SourceID
					WHERE		AuthorTypeShortCode = 'Ind'
							AND c.Role = @Role
							AND ca.Role = @Role
							AND s.Role = @Role
							AND p.Role = @Role
							AND t.Role = @Role
							AND ca.CountryAgainst =	CASE
											WHEN @CountryAgainst = '0' THEN ca.CountryAgainst
											ELSE @CountryAgainst
										END
							AND ca.IndexNumber =	CASE
											WHEN @IndexNumber > 0 THEN @IndexNumber
											ELSE 0
										END
							AND ca.AuthorTypeCode =	CASE
											WHEN @AuthorType = '0' THEN ca.AuthorTypeCode
											ELSE @AuthorType
										END
							AND ca.Author LIKE	CASE
											WHEN @Author = '0' THEN ca.Author
											ELSE '%' + @Author + '%'
										END
							AND Year(c.LetterDateStart) =	CASE
												WHEN @Year > 0 THEN @Year
												ELSE Year(c.LetterDateStart)
											END
							AND ca.Summary LIKE	CASE
											WHEN @Summary = '0' THEN ca.Summary
											ELSE '%' + @Summary + '%'
										END

					UNION ALL

					SELECT		CaseID,
							AuthorTypeShortCode,
							t.AuthorTypeCode,
							CaseID AS [ID],
							CaseCode,
							IndexNumber,
							ca.CountryAgainst,
							Summary,
							c.Comments,
							CONVERT(CHAR(10), LetterDateStart, 103) AS LetterDateStart,
							CONVERT(CHAR(10), LetterDateEnd, 103) AS LetterDateEnd,
							LetterDateSource,
							LetterLanguage,
							SeenBy1,
							SeenBy2,
							SeenBy3,
							Cleared,
							CONVERT(CHAR(10), Sent_Date, 103) AS Sent_Date,
							Type_Ack,
							s.SourceID,
							Author
							Author AS FirstName,
							OfficialName AS LastName,
							p.Address,
							p.CountryID,
							p.Head,
							p.City,
							p.Tel,
							p.Fax,
							CONVERT(CHAR(10), c.RegistryDate, 103) AS RegistryDate,
							p.Type AS OrgAuthorType
					FROM		Complaint AS c
					INNER JOIN	[1503_Case] AS ca ON ca.ComplaintID = c.ComplaintID
					INNER JOIN	[1503_SourceDetails] AS s ON s.ComplaintID = c.ComplaintID
					INNER JOIN	[1503_Institution] AS p ON p.InstitutionID = s.SourceID
					INNER JOIN	[1503_AuthorType] AS t ON t.AuthorTypeCode = s.AuthorTypeCode
					WHERE		c.Role = @Role
							AND ca.Role = @Role
							AND s.Role = @Role
							AND p.Role = @Role
							AND t.Role = @Role
							AND ca.CountryAgainst =	CASE
											WHEN @CountryAgainst = '0' THEN ca.CountryAgainst
											ELSE @CountryAgainst
										END
							AND ca.IndexNumber =	CASE
											WHEN @IndexNumber > 0 THEN @IndexNumber
											ELSE 0
										END
							AND ca.AuthorTypeCode =	CASE
											WHEN @AuthorType = '0' THEN ca.AuthorTypeCode
											ELSE @AuthorType
										END
							AND ca.Author LIKE	CASE
											WHEN @Author = '0' THEN ca.Author
											ELSE '%' + @Author + '%'
										END
							AND Year(c.LetterDateStart) =	CASE
												WHEN @Year > 0 THEN @Year
												ELSE Year(c.LetterDateStart)
											END
							AND ca.Summary LIKE	CASE
											WHEN @Summary = '0' THEN ca.Summary
											ELSE '%' + @Summary + '%'
										END
				) AS d
		) AS q
WHERE		RecID > @PageSize * (@PageIndex - 1)
		AND RecID <= @PageSize * @PageIndex
ORDER BY	CaseID


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2007-08-27 : 14:50:06
... or, if you must use dynamic sql, just use sp_executeSQL, which lets you pass parameter values into a dynamic sql statement.

The solution used -- direct concatenation with a sql statement -- is vulnerable to sql injection and escaping issues.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-08-27 : 14:53:51
Or, for easier maintenance and speedier retreival, use this.

First of all, create a VIEW containing the base resultset you are interested in
CREATE VIEW	dbo.vwPagination
AS

SELECT		c.RoleID,
		CaseID,
		AuthorTypeShortCode,
		t.AuthorTypeCode,
		CaseID AS [ID],
		CaseCode,
		IndexNumber,
		ca.CountryAgainst,
		Summary,
		c.Comments,
		DATEADD(DAY, DATEDIFF(DAY, 0, LetterDateStart), 0) AS LetterDateStart,
		DATEADD(DAY, DATEDIFF(DAY, 0, LetterDateEnd), 0) AS LetterDateEnd,
		LetterDateSource,
		LetterLanguage,
		SeenBy1,
		SeenBy2,
		SeenBy3,
		Cleared,
		DATEADD(DAY, DATEDIFF(DAY, 0, Sent_Date), 0) AS Sent_Date,
		Type_Ack,
		s.SourceID,
		Author,
		'' AS FirstName,
		'' AS LastName,
		Address,
		CountryID,
		'' AS Head,
		'' AS OrgCity,
		'' AS OrgTel,
		'' AS OrgFax,
		DATEADD(DAY, DATEDIFF(DAY, 0, c.RegistryDate), 0) AS RegistryDate,
		'' AS OrgAuthorType
FROM		Complaint AS c
INNER JOIN	[1503_Case] AS ca ON ca.ComplaintID = c.ComplaintID AND ca.Role = c.Role
INNER JOIN	[1503_SourceDetails] AS s ON s.ComplaintID = c.ComplaintID AND s.Role = c.Role
INNER JOIN	[1503_AuthorType] AS t ON t.AuthorTypeCode = s.AuthorTypeCode AND t.Role = s.Role
WHERE		SourceID = 0

UNION ALL

SELECT		c.RoleID,
		CaseID,
		AuthorTypeShortCode,
		t.AuthorTypeCode,
		CaseID AS [ID],
		CaseCode,
		IndexNumber
		ca.CountryAgainst,
		Summary,
		c.Comments,
		DATEADD(DAY, DATEDIFF(DAY, 0, LetterDateStart), 0) AS LetterDateStart,
		DATEADD(DAY, DATEDIFF(DAY, 0, LetterDateEnd), 0) AS LetterDateEnd,
		LetterDateSource,
		LetterLanguage,
		SeenBy1,
		SeenBy2,
		SeenBy3,
		Cleared,
		DATEADD(DAY, DATEDIFF(DAY, 0, Sent_Date), 0) AS Sent_Date,
		Type_Ack,
		s.SourceID,
		FirstName + ', ' + LastName AS Author,
		FirstName,
		LastName,
		p.Address,
		p.CountryID,
		'' AS Head,
		'' AS OrgCity,
		'' AS OrgTel,
		'' AS OrgFax,
		DATEADD(DAY, DATEDIFF(DAY, 0, c.RegistryDate), 0) AS RegistryDate,
		'' AS OrgAuthorType
FROM		Complaint AS c
INNER JOIN	[1503_Case] AS ca ON ca.ComplaintID = c.ComplaintID AND ca.Role = c.Role
INNER JOIN	[1503_SourceDetails] AS s ON s.ComplaintID = c.ComplaintID AND s.Role = c.Role
INNER JOIN	[1503_AuthorType] AS t ON t.AuthorTypeCode = s.AuthorTypeCode AND t.Role = s.Role
INNER JOIN	Person AS p ON p.PersonID = s.SourceID AND p.Role = s.Role
WHERE		AuthorTypeShortCode = 'Ind'

UNION ALL

SELECT		c.RoleID,
		CaseID,
		AuthorTypeShortCode,
		t.AuthorTypeCode,
		CaseID AS [ID],
		CaseCode,
		IndexNumber,
		ca.CountryAgainst,
		Summary,
		c.Comments,
		DATEADD(DAY, DATEDIFF(DAY, 0, LetterDateStart), 0) AS LetterDateStart,
		DATEADD(DAY, DATEDIFF(DAY, 0, LetterDateEnd), 0) AS LetterDateEnd,
		LetterDateSource,
		LetterLanguage,
		SeenBy1,
		SeenBy2,
		SeenBy3,
		Cleared,
		DATEADD(DAY, DATEDIFF(DAY, 0, Sent_Date), 0) AS Sent_Date,
		Type_Ack,
		s.SourceID,
		Author
		Author AS FirstName,
		OfficialName AS LastName,
		p.Address,
		p.CountryID,
		p.Head,
		p.City,
		p.Tel,
		p.Fax,
		DATEADD(DAY, DATEDIFF(DAY, 0, c.RegistryDate), 0) AS RegistryDate,
		p.Type AS OrgAuthorType
FROM		Complaint AS c
INNER JOIN	[1503_Case] AS ca ON ca.ComplaintID = c.ComplaintID AND ca.Role = c.Role
INNER JOIN	[1503_SourceDetails] AS s ON s.ComplaintID = c.ComplaintID AND s.Role = c.Role
INNER JOIN	[1503_Institution] AS p ON p.InstitutionID = s.SourceID AND p.Role = s.Role
INNER JOIN	[1503_AuthorType] AS t ON t.AuthorTypeCode = s.AuthorTypeCode AND t.Role = s.Role
Now create the stored procedure
CREATE PROCEDURE dbo.sp_Select_1503SmallCard
(
	@Author VARCHAR(500),
	@AuthorType VARCHAR(50),
	@CountryAgainst VARCHAR(3),
	@IndexNumber BIGINT,
	@Year VARCHAR(4),
	@Search VARCHAR(10),
	@Summary VARCHAR(100),
	@PageSize INT = 1,
	@PageIndex INT,
	@Role VARCHAR(20) = '1503' 
)
AS

SET NOCOUNT ON

SET	@Author = REPLACE(@Author, ' ', '%')

SELECT		CaseID,
		AuthorTypeShortCode,
		AuthorTypeCode,
		[ID],
		CaseCode,
		IndexNumber,
		CountryAgainst,
		Summary,
		Comments,
		CONVERT(CHAR(10), LetterDateStart, 103) AS LetterDateStart,
		CONVERT(CHAR(10), LetterDateEnd, 103) AS LetterDateEnd,
		LetterDateSource,
		LetterLanguage,
		SeenBy1,
		SeenBy2,
		SeenBy3,
		Cleared,
		CONVERT(CHAR(10), Sent_Date, 103) AS Sent_Date,
		Type_Ack,
		SourceID,
		Author,
		FirstName,
		LastName,
		Address,
		CountryID,
		Head,
		OrgCity,
		OrgTel,
		OrgFax,
		CONVERT(CHAR(10), RegistryDate, 103) AS RegistryDate,
		OrgAuthorType
FROM		(
			SELECT	CaseID,
				AuthorTypeShortCode,
				AuthorTypeCode,
				[ID],
				CaseCode,
				IndexNumber,
				CountryAgainst,
				Summary,
				Comments,
				LetterDateStart ,
				LetterDateEnd,
				LetterDateSource,
				LetterLanguage,
				SeenBy1,
				SeenBy2,
				SeenBy3,
				Cleared,
				Sent_Date,
				Type_Ack,
				SourceID,
				Author,
				FirstName,
				LastName,
				Address,
				CountryID,
				Head,
				OrgCity,
				OrgTel,
				OrgFax,
				RegistryDate,
				OrgAuthorType,
				ROW_NUMBER() OVER (ORDER BY CaseID) AS RecID
			FROM	dbo.vwPagination
			WHERE	Role = @Role
				AND IndexNumber =	CASE
								WHEN @IndexNumber > 0 THEN @IndexNumber
								ELSE 0
							END
				AND CountryAgainst =	CASE
								WHEN @CountryAgainst = '0' THEN CountryAgainst
								ELSE @CountryAgainst
							END
				AND AuthorTypeCode =	CASE
								WHEN @AuthorType = '0' THEN AuthorTypeCode
								ELSE @AuthorType
							END
				AND Author LIKE	CASE
								WHEN @Author = '0' THEN Author
								ELSE '%' + @Author + '%'
							END
				AND Year(LetterDateStart) =	CASE
									WHEN @Year > 0 THEN @Year
									ELSE Year(LetterDateStart)
								END
				AND Summary LIKE	CASE
								WHEN @Summary = '0' THEN Summary
								ELSE '%' + @Summary + '%'
							END
		) AS d
WHERE		RecID > @PageSize * (@PageIndex - 1)
		AND RecID <= @PageSize * @PageIndex
ORDER BY	CaseID

E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2007-08-27 : 15:04:55
It looks like all 3 union's can be replaced with a single SELECT as well by simply altering the WHERE clause a little (at first glance, anyway). I don't have the time or patience to analyze it and make the change, but I bet Peso does !!

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-08-27 : 15:15:07
I looked at it, but it will mess up the pagination.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-08-27 : 15:29:41
You could try this VIEW
CREATE VIEW	dbo.vwPagination
AS

SELECT		c.RoleID,
		CaseID,
		AuthorTypeShortCode,
		t.AuthorTypeCode,
		CaseID AS [ID],
		CaseCode,
		IndexNumber,
		ca.CountryAgainst,
		Summary,
		c.Comments,
		DATEADD(DAY, DATEDIFF(DAY, 0, LetterDateStart), 0) AS LetterDateStart,
		DATEADD(DAY, DATEDIFF(DAY, 0, LetterDateEnd), 0) AS LetterDateEnd,
		LetterDateSource,
		LetterLanguage,
		SeenBy1,
		SeenBy2,
		SeenBy3,
		Cleared,
		DATEADD(DAY, DATEDIFF(DAY, 0, Sent_Date), 0) AS Sent_Date,
		Type_Ack,
		s.SourceID,
		CASE
			WHEN AuthorTypeShortCode = 'Ind' THEN FirstName + ', ' + LastName
			ELSE Author
		END AS Author,
		CASE
			WHEN p.InstitutionID IS NULL THEN ''
			ELSE Author
		END AS FirstName,
		CASE
			WHEN p.InstitutionID IS NULL THEN ''
			ELSE OfficialName
		END AS LastName,
		Address,
		CountryID,
		CASE
			WHEN WHEN p.InstitutionID IS NULL THEN ''
			ELSE p.Head 
		END AS Head,
		CASE
			WHEN WHEN p.InstitutionID IS NULL THEN ''
			ELSE p.City 
		END AS OrgCity,
		CASE
			WHEN WHEN p.InstitutionID IS NULL THEN ''
			ELSE p.Tel 
		END AS OrgTel,
		CASE
			WHEN WHEN p.InstitutionID IS NULL THEN ''
			ELSE p.Type
		END AS OrgFax,
		DATEADD(DAY, DATEDIFF(DAY, 0, c.RegistryDate), 0) AS RegistryDate,
		CASE
			WHEN WHEN p.InstitutionID IS NULL THEN ''
			ELSE p.Fax
		END AS OrgAuthorType
FROM		Complaint AS c
INNER JOIN	[1503_Case] AS ca ON ca.ComplaintID = c.ComplaintID AND ca.Role = c.Role
INNER JOIN	[1503_SourceDetails] AS s ON s.ComplaintID = c.ComplaintID AND s.Role = c.Role
INNER JOIN	[1503_AuthorType] AS t ON t.AuthorTypeCode = s.AuthorTypeCode AND t.Role = s.Role
LEFT JOIN	Person AS p ON p.PersonID = s.SourceID AND p.Role = s.Role
LEFT JOIN	[1503_Institution] AS p ON p.InstitutionID = s.SourceID AND p.Role = s.Role

E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts
Posted - 2007-08-27 : 16:00:14
this is a seriously thorough answer to the original question. Well formatted too

Nice job!



-ec
Go to Top of Page

Jenda
Starting Member

29 Posts
Posted - 2007-08-27 : 19:12:04
quote:
Originally posted by Mari



CREATE PROCEDURE dbo.sp_Select_1503SmallCard



Let me quote MSDN: "Do not prefix stored procedures with sp_, which is a prefix reserved for identifying system stored procedures." ([url]http://msdn2.microsoft.com/en-us/library/aa291593(vs.71).aspx[/url])

Jenda
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site