I'm very beginner in writing sql scripts,
while trying to write the following script and execute it, i could not send null values into the procedure.
could someone help me out from the following code??
ALTER PROCEDURE SP_TALKTRACK2
-- Add the parameters for the stored procedure here
@city VARCHAR(100) = NULL,
@state VARCHAR(100)= NULL,
@zip VARCHAR(100) = NULL,
@gender VARCHAR(100)= NULL
AS
BEGIN
/*
DECLARE @CITY VARCHAR(100)
DECLARE @STATE VARCHAR(100)
DECLARE @ZIP VARCHAR(100)
DECLARE @GENDER VARCHAR(100) */
DECLARE @TOTALMAKES INT
DECLARE @DESCRIPTION VARCHAR(100)
DECLARE @MAKE2 INT
SET @CITY = @city
SET @STATE = @state
SET @ZIP = @zip
SET @GENDER = @gender
SET @DESCRIPTION = ' MAKES ARE BEING USED IN ' + @CITY + ' ' + @STATE + ' ' + @ZIP
SET @MAKE2 = (SELECT COUNT(MAKE)FROM [TALK TRACK RAP].[DBO].[CARSEXCEL]
WHERE CITY = @CITY AND STATE = @STATE AND ZIP = @ZIP AND GENDER = @GENDER)
SELECT MAKE , ( (100 * COUNT(*)) /@MAKE2 ) AS MAKECOUNT ,
CONVERT(VARCHAR, ( (100 * COUNT(*)) /@MAKE2 ))+ ' % ' + MAKE + @DESCRIPTION AS MAKES_PERCENTAGE INTO ##TEMP1
FROM [TALK TRACK RAP].[DBO].[CARSEXCEL]
WHERE CITY = @CITY AND STATE = @STATE AND ZIP = @ZIP AND GENDER = @GENDER
GROUP BY MAKE
SELECT * FROM ##TEMP1 where MAKECOUNT <> 0 order by MAKECOUNT desc
DROP TABLE ##TEMP1
SET NOCOUNT ON;
-- Insert statements for procedure here
END
GO
In the above if i execute leaving one parameter i'm getting null records because i don't have null data
EXEC SP_TALKTRACK2 @CITY = 'ODESSA', @STATE = 'TX', @ZIP = '79762', @GENDER = 'FEMALE'
But what i need is, it should ignore that parameter and run considering only the other 3 values or 2 values what ever i provide.
Thanks in advance for your help!!
Thanks, Pradeep
use ISNULL if first parameter is null then return secound parameter for for example ISNULL(@CITY, '') if @CITY is null return empty string becuase secound parameter is empty string this check is required becuse if you add null to string the result is null!
and also in where condation you must check if @city is null dont check city column by null like :
@City is null or City = @City
if your City column also is nullable you can check this :
((@City is null AND City is null) OR (City = @City))
and you'r procedure must like this :
ALTER PROCEDURE SP_TALKTRACK2
-- Add the parameters for the stored procedure here
@city VARCHAR(100) = NULL,
@state VARCHAR(100)= NULL,
@zip VARCHAR(100) = NULL,
@gender VARCHAR(100)= NULL
AS
BEGIN
/*
DECLARE @CITY VARCHAR(100)
DECLARE @STATE VARCHAR(100)
DECLARE @ZIP VARCHAR(100)
DECLARE @GENDER VARCHAR(100) */
DECLARE @TOTALMAKES INT
DECLARE @DESCRIPTION VARCHAR(100)
DECLARE @MAKE2 INT
SET @CITY = @city
SET @STATE = @state
SET @ZIP = @zip
SET @GENDER = @gender
SET @DESCRIPTION = ' MAKES ARE BEING USED IN ' + ISNULL(@CITY, '') + ' ' + ISNULL(@STATE, '') + ' ' + ISNULL(@ZIP, '')
SET @MAKE2 = (SELECT COUNT(MAKE)FROM [TALK TRACK RAP].[DBO].[CARSEXCEL]
WHERE (@city is null OR CITY = @CITY) AND (@state is null or [STATE] = @STATE) AND (@zip is null or ZIP = @ZIP) AND (@gender is null or GENDER = @GENDER))
SELECT MAKE , ( (100 * COUNT(*)) /@MAKE2 ) AS MAKECOUNT ,
CONVERT(VARCHAR, ( (100 * COUNT(*)) /@MAKE2 ))+ ' % ' + MAKE + @DESCRIPTION AS MAKES_PERCENTAGE INTO ##TEMP1
FROM [TALK TRACK RAP].[DBO].[CARSEXCEL]
WHERE (@city is null OR CITY = @CITY) AND (@state is null or [STATE] = @STATE) AND (@zip is null or ZIP = @ZIP) AND (@gender is null or GENDER = @GENDER)
GROUP BY MAKE
SELECT * FROM ##TEMP1 where MAKECOUNT <> 0 order by MAKECOUNT desc
DROP TABLE ##TEMP1
SET NOCOUNT ON;
-- Insert statements for procedure here
END
GO
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
몇 마디 만하겠습니다