none
Trouble with seperating inputs

    Question

  • I am still new to the world of SQL, and I am trying to create a report with two input values, all of which can be different.

    For example, I want to pull all active clients and for the first input the user would put 'all' however they must also enter in a title for clients, such as 'technology'. So the user would enter in 'all:tech' which should return ALL clients that are in Technology. However the clients should be able to enter in anything they want, with a max character length of 16 characters. Here is what I have so far, I hope this is enough for some of our stellar folks to spearhead this with me.

    DECLARE

    @CStauts VARCHAR(16)

    DECLARE

    @Title VARCHAR(16)

    DECLARE

    @data VARCHAR(64)

    SET

    @CStauts = SUBSTRING(@data,1,3)

    SET

    @Title = '%'+ SUBSTRING(@data,1,3) + '%'

    IF

    @CStauts = 'ALL' AND @Title = ''

    Tuesday, July 30, 2013 1:38 PM

Answers

  • HI Dear,

    Here is what you wanted...

    create table Clients (ClientName varchar(500), Department varchar(100))
    go
    insert into Clients 
    values ('Client1', 'Technology'),('Client2', 'Technology'),('Client3', 'Technology'),
    ('Client4', 'HR'),('Client5', 'HR'),('Client6', 'HR'),
    ('Client7', 'IT'),('Client8', 'IT'),('Client9', 'IT')
    go
    select * from Clients
    go
    Create proc spGetClients 
    (
    @Input varchar(500)='all:tech'
    )
    as
    begin
    declare @ClientName varchar(500), @Dep varchar(100);
    select @ClientName=substring(@Input, 1,charindex(':',@Input,0)-1), 
    @Dep=substring(@Input, charindex(':',@Input,0)+1,len(@Input))

    print @ClientName
    print @Dep

    if @ClientName = 'ALL'
    begin
    select ClientName, Department 
    from Clients where Department =@Dep;
    end

    else 
    begin
    select ClientName, Department from Clients 
    where ClientName=@ClientName
    and Department =@Dep;
    end
    end

    go

    EXEC spGetClients  'Client1:Technology'
    go


    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, July 30, 2013 2:29 PM

All replies

  • HI Dear,

    Here is what you wanted...

    create table Clients (ClientName varchar(500), Department varchar(100))
    go
    insert into Clients 
    values ('Client1', 'Technology'),('Client2', 'Technology'),('Client3', 'Technology'),
    ('Client4', 'HR'),('Client5', 'HR'),('Client6', 'HR'),
    ('Client7', 'IT'),('Client8', 'IT'),('Client9', 'IT')
    go
    select * from Clients
    go
    Create proc spGetClients 
    (
    @Input varchar(500)='all:tech'
    )
    as
    begin
    declare @ClientName varchar(500), @Dep varchar(100);
    select @ClientName=substring(@Input, 1,charindex(':',@Input,0)-1), 
    @Dep=substring(@Input, charindex(':',@Input,0)+1,len(@Input))

    print @ClientName
    print @Dep

    if @ClientName = 'ALL'
    begin
    select ClientName, Department 
    from Clients where Department =@Dep;
    end

    else 
    begin
    select ClientName, Department from Clients 
    where ClientName=@ClientName
    and Department =@Dep;
    end
    end

    go

    EXEC spGetClients  'Client1:Technology'
    go


    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, July 30, 2013 2:29 PM
  • That was actually close, however maybe I need to reword my question. What I am specifically trying to do is return what is to the right of the ':'

    DECLARE

    @CStauts VARCHAR(16)

    DECLARE

    @Title VARCHAR(16)

    DECLARE

    @data VARCHAR(64)

    DECLARE

    @ColIndex INT

    SET @data = '72:PRI'

    SET @ColIndex = PATINDEX(@data,'%:%0)

    --SET @CStauts = SUBSTRING(@data, 1,@ColIndex-1)

    --SET @Title = '

    %'+ SUBSTRING(@data,1,3) + '%'Maybe something like this?
    Wednesday, July 31, 2013 6:39 PM
  • Use CharIndex instead of Patindex as,

    declare @data varchar(10)
    SET @data = '72:PRI'
    select right(@data,charindex(':',@data,1))


    Regards, RSingh

    Friday, September 27, 2013 9:23 AM
    Moderator