?
Solved

Dynamic "where" statement (Stored procedure)

Posted on 2008-06-13
2
Medium Priority
?
367 Views
Last Modified: 2008-06-13
Se attached code snippet. I need help to make the "whereStatement" correct.

If @guid is NOT blank I want the where statement to be like this:
where active='1'  and guid=@guid

If @guid is blank I want the where statement to be like this:
where active='1'  and codeID=@codeID

Thanks for all help :)
ALTER PROCEDURE [dbo].[spKodeDetaljPay] 
(
@codeid smallint,
@guid varchar(64)
)
AS
 
BEGIN
 
Select title, text, 
(Select name from tblPersonPay where tblCode.PersonID = tblPersonPay.PersonID) as Name,
(Select email from tblPersonPay where tblCode.PersonID = tblPersonPay.PersonID) as email from tblCode
where active='1' -- and " & whereStatement 
 
END

Open in new window

0
Comment
Question by:webressurs
2 Comments
 
LVL 6

Accepted Solution

by:
Compaq_Engineer earned 500 total points
ID: 21779054
Try;-
ALTER PROCEDURE [dbo].[spKodeDetaljPay]
(
@codeid smallint,
@guid varchar(64)
)
AS
 
BEGIN
 
Select title, text,
(Select name from tblPersonPay where tblCode.PersonID = tblPersonPay.PersonID) as Name,
(Select email from tblPersonPay where tblCode.PersonID = tblPersonPay.PersonID) as email from tblCode
where
(active='1' and guid=@guid)
OR
(active='1' and codeid=@codeid and @guid='')
 
END
0
 
LVL 16

Assisted Solution

by:brad2575
brad2575 earned 500 total points
ID: 21779089
have to set up a query into a string and execute the string

DECLARE @query as varchar(MAX)
 
Set @query = 'Select title, text, ' +
	'(Select name from tblPersonPay where tblCode.PersonID = tblPersonPay.PersonID) as Name, ' +
	'(Select email from tblPersonPay where tblCode.PersonID = tblPersonPay.PersonID) as email from tblCode ' +
	'where active=''1''  '
 
IF @guid = '' OR @guid IS NULL
    Set @query = @query + 'AND guid=''' + @guid + ''' '
ELSE 
	Set @query = @query + 'AND codeID=''' + @codeID  + ''' '
 
EXEC(@query)

Open in new window

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
This video tutorial shows you the steps to go through to set up what I believe to be the best email app on the android platform to read Exchange mail.  Get the app on your phone: The first step is to make sure you have the Samsung Email app on your …
Suggested Courses

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question