Click here to Skip to main content
Rate this: bad
good
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
@serchtext='city,sate'
how to split city ,sate separate my out put like:
@city=city
@state=state
my query like this
ALTER PROCEDURE [dbo].[SP_TESTSEARCH](@Mode int,@INDUSTRY varchar(100),@JOB_TYPE varchar(100),@SERCHTEXT VARCHAR(100))
AS
BEGIN
 
Declare @city varchar(100);
Declare @sta varchar(100);
SET @city= SUBSTRING(@LOCATION,0,CHARINDEX(',',@LOCATION,0))
 
Set @sta= SUBSTRING(@LOCATION,(CHARINDEX(',',@LOCATION,0)+1),(lEN(@LOCATION)-(CHARINDEX(',',@LOCATION,0))))
if(@sta is null)
begin
set @sta=''
end
if(@city is null)
begin
set @city=''
end
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
v3
Comments
Sandeep Mewara at 20-Oct-12 3:27am
   
Not too clear, but are you looking for:
exec SP_TESTSEARCH 2,NULL,NULL,'city'

1 solution

Rate this: bad
good
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.
  Permalink  

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



Advertise | Privacy | Mobile
Web01 | 2.8.1411022.1 | Last Updated 20 Oct 2012
Copyright © CodeProject, 1999-2014
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