Click here to Skip to main content
11,721,053 members (83,326 online)
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 20:43pm
Edited 19-Oct-12 21: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 500
1 OriginalGriff 334
2 Mika Wendelius 160
3 F-ES Sitecore 140
4 Maciej Los 80
0 Sergey Alexandrovich Kryukov 2,199
1 OriginalGriff 959
2 F-ES Sitecore 640
3 Maciej Los 609
4 Richard MacCutchan 540

Advertise | Privacy | Mobile
Web03 | 2.8.150901.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