Click here to Skip to main content
Rate this: bad
Please Sign up or sign in to vote.
See more: SQL Server
hi all,
how to split and set the empty values in sql server using stored procedure like
how to split city ,sate separate my out put like:
my query like this
ALTER PROCEDURE [dbo].[SP_TESTSEARCH](@Mode int,@INDUSTRY varchar(100),@JOB_TYPE varchar(100),@SERCHTEXT VARCHAR(100))
Declare @city varchar(100);
Declare @sta varchar(100);
if(@sta is null)
set @sta=''
if(@city is null)
set @city=''
how to write a query for split and set null or empty the values for declare
variables am search like in:
exec SP_TESTSEARCH 2,'','','city'
i didn't get any error but display the all records.
am exec the procedure like this:
exec SP_TESTSEARCH 2,'','','city,''' 
i got the matching records
how to set empty values in SP
thanking you
Posted 19-Oct-12 21:43pm
Edited 19-Oct-12 22:25pm
Sandeep Mewara at 20-Oct-12 3:27am
Not too clear, but are you looking for:

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Your question is at best incomplete.
For starters the code you posted does not define '@LOCATION'. So you are certainly never going to get a city or state from that.
Also I am just ignoring what "all records" means since it doesn't mean anything for the code posted.
I think your requirements are lacking. Based on your question you think you should be able to handle a value with no comma. But your code does not differentiate that. A value without a comma could be EITHER a city or a state and the requirements, not the code must define that.
Your code works, if @LOCATION is defined, for the following cases.
- City and state is provided.
- City with comma is provided.
- State with comma is provided.
It doesn't work if the comma is lacking but, as I said, that is failure in the requirements not the code (at this point.)
Additionally SUBSTRING will never return null unless @LOCATION is null. So the subsequent if checks will only do something if @location is null.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 449
1 Maciej Los 430
2 OriginalGriff 320
3 CHill60 240
4 CPallini 210
0 Sergey Alexandrovich Kryukov 10,102
1 OriginalGriff 9,495
2 Peter Leow 5,241
3 Kornfeld Eliyahu Peter 3,373
4 Maciej Los 3,076

Advertise | Privacy | Mobile
Web03 | 2.8.150327.1 | Last Updated 20 Oct 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100