how to write this stored procedure

Question

4.00/5 (1 vote)

See more:

Hi,

I have this stored procedure:

SQL

CREATE PROCEDURE dbo.uspSelectAllTopicID

    @SortType char
AS
if(@SortType='A')
    begin
        select TopicID from  Topics where TopicDelete =0 order by TopicID
    end
else if(@SortType='B')
    begin
        select TopicID from  Topics where TopicDelete =1 order by TopicID
    end
else if(@SortType='C')
    begin
        select TopicID from  Topics order by TopicID
    end
GO

but all differece is in where phrase only, so i tried to write it as:

SQL

CREATE PROCEDURE dbo.uspSelectAllTopicID

    @SortType char
AS
select TopicID from  Topics
if(@SortType='A')
    begin
        where TopicDelete =0
    end
else if(@SortType='B')
    begin
        where TopicDelete =1
    end
order by TopicID
GO

but it give me an error
So, what is the right?

Posted 28-Feb-11 2:04am

MrLonely_2

Add a Solution

4 solutions

Solution 2

Hi,

Can you try the following by passing the sort type as integer value from the code itself and directly using it

CREATE PROCEDURE dbo.uspSelectAllTopicID
@SortType int
AS

select TopicID from Topics where TopicDelete =@SortType order by TopicID

GO

Posted 28-Feb-11 2:18am

sudheshna123

Comments

MrLonely_2 28-Feb-11 8:21am

thank, but in last case where param='C', i do not use "Where" phrase

Solution 3

Use the stored procedure sp_executesql the second example on this page here is spot on for you:
http://msdn.microsoft.com/de-de/library/ms188001.aspx[^]

SQL

CREATE PROCEDURE dbo.uspSelectAllTopicID
    @SortType char
AS
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @IntVal tinyint;

if(@SortType='A')
    begin
        SET @IntVal = 0;
    end
else if(@SortType='B')
    begin 
        SET @IntVal = 1;
    end

SET @SQLString = N'select TopicID from  Topics where TopicDelete = @level order by TopicID';
SET @ParmDefinition = N'@level tinyint';

EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVal;
GO

Posted 28-Feb-11 2:22am

Manfred Rudolf Bihy

Updated 28-Feb-11 2:35am

v3

Solution 4

There is nothing wrong with your first version. Maybe adding a condition for the wrong @SortType (not being A,B,C):

SQL

else
    begin
    select  TopicID
    from    Topics
    where   0 = 1
    order
    by      TopicID
    end

The second version could be like:

SQL

select  TopicID
from    Topics
where   1 = case
                when @SortType='A' and TopicDelete = 0  then 1
                when @SortType='B' and TopicDelete = 1  then 1
                when @SortType='C'                      then 1
                else 0
            end
order
by      TopicID

Posted 28-Feb-11 2:53am

Costica U

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)

#realJSOP · Accepted Answer · 2011-02-28T02:15:00

There are more elegant ways, but tyhis should work:

SQL

declare @whereClause varchar(64)
set @whereClaus = ''
if (@SortType='A')
    begin 
        set @whereClause = 'where TopicDelete=0 '
    end
else if (@SortType='B')
    begin
        set @whereClause = 'where TopicDelete=1 ' 
    end
declare @sql varchar(1024)
set @sql = 'select TopicID from Topics ' + @whereClause + 'order by TopicID'
exec(@sql)