June 20, 2011 at 8:53 am
Hi All,
I have a Stored procedure that returns results based on the State that they are in, (e.g. 'CA' for California).
I want to modify it so that if the user selects a Director Region, that it will display all the results for the given Director.
This is what I have int the Stored Procedure;
USE [MW]
GO
/****** Object: StoredProcedure [dbo].[GetMapLocationsByState] Script Date: 06/20/2011 14:29:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetMapLocationsByState]
@LocationState nvarchar(2)
AS
BEGIN
SET NOCOUNT ON;
SELECT [LocationID]
,[LocationSub]
,[LocationName]
,[LocationState]
,[LocationStatus]
,[Latitude]
,[Longitude]
,[Notes]
,[Director]
FROM [PTC].[dbo].[MapLocations]
WHERE LocationState = @LocationState
END
How can I impliment an IF type statement in this SP?
Thanks,
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
June 20, 2011 at 9:08 am
Will you be allowing them 2 fields to fill in or just 1? i.e. Will the procedure take 2 parameters and return results based on that? I would take 2 parameters and set up 3 queries with if statements for the following 3 cases: director is null or blank and state is not null or not blank, director is not null or not blank and state is not null or not blank, director is not null or not blank and state is null or blank.
Is this what you are thinking?
Thanks,
Jared
Jared
CE - Microsoft
June 20, 2011 at 9:12 am
It would be either the All States, or the individual States.
Not both parametes.
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
June 20, 2011 at 9:14 am
What about for the Director? I was thinking something like this:
ALTER PROCEDURE [dbo].[GetMapLocationsByState]
@LocationState nvarchar(2), @Director nvarchar(100)
AS
BEGIN
SET NOCOUNT ON;
IF (@LocationState IS NULL OR @LocationState = '') AND (@Director IS NOT NULL OR @Director <> '')
BEGIN
SELECT [LocationID]
,[LocationSub]
,[LocationName]
,[LocationState]
,[LocationStatus]
,[Latitude]
,[Longitude]
,[Notes]
,[Director]
FROM [PTC].[dbo].[MapLocations]
WHERE Director = @Director
END
IF (@LocationState IS NOT NULL OR @LocationState <> '') AND (@Director IS NOT NULL OR @Director <> '')
BEGIN
SELECT [LocationID]
,[LocationSub]
,[LocationName]
,[LocationState]
,[LocationStatus]
,[Latitude]
,[Longitude]
,[Notes]
,[Director]
FROM [PTC].[dbo].[MapLocations]
WHERE Director = @Director AND LocationState = @LocationState
END
IF (@LocationState IS NOT NULL OR @LocationState <> '') AND (@Director IS NULL OR @Director = '')
BEGIN
SELECT [LocationID]
,[LocationSub]
,[LocationName]
,[LocationState]
,[LocationStatus]
,[Latitude]
,[Longitude]
,[Notes]
,[Director]
FROM [PTC].[dbo].[MapLocations]
WHERE LocationState = @LocationState
END
END
Thanks,
Jared
Jared
CE - Microsoft
June 20, 2011 at 10:47 am
Here is a possible way of achieving what you need.
alter PROCEDURE [dbo].[GetMapLocationsByState]
@LocationState nvarchar(2)= NULL
,@Director VARCHAR(5) = NULL
AS
DECLARE @Tsql NVARCHAR(300)
IF @LocationState IS NULL AND @Director IS NULL
BEGIN
PRINT 'Invalid null input'
RETURN
END
IF @LocationState IS NOT NULL AND @Director IS NOT NULL
BEGIN
PRINT 'Invalid state & director input'
RETURN
END
SET @Tsql = 'SELECT [LocationID],[LocationSub],[LocationName]
,[LocationState],[LocationStatus],[Latitude]
,[Longitude] ,[Notes],[Director]
FROM MapLocations WHERE '
IF @LocationState IS NOT NULL
SET @Tsql = @Tsql + 'LocationState = ' +'''' + @LocationState + ''''
IF @Director IS NOT NULL
SET @Tsql = @Tsql + 'Director = ' + '''' + @Director + ''''
BEGIN
SET NOCOUNT ON
--PRINT @Tsql --use print during testing & checking
EXECUTE sp_executesql @Tsql
END
Run AS:
[GetMapLocationsByState] 'ca',NULL
[GetMapLocationsByState] NULL,'xyz'
June 20, 2011 at 11:10 am
bitbucket-25253 (6/20/2011)
Here is a possible way of achieving what you need.
alter PROCEDURE [dbo].[GetMapLocationsByState]@LocationState nvarchar(2)= NULL
,@Director VARCHAR(5) = NULL
AS
DECLARE @Tsql NVARCHAR(300)
IF @LocationState IS NULL AND @Director IS NULL
BEGIN
PRINT 'Invalid null input'
RETURN
END
IF @LocationState IS NOT NULL AND @Director IS NOT NULL
BEGIN
PRINT 'Invalid state & director input'
RETURN
END
SET @Tsql = 'SELECT [LocationID],[LocationSub],[LocationName]
,[LocationState],[LocationStatus],[Latitude]
,[Longitude] ,[Notes],[Director]
FROM MapLocations WHERE '
IF @LocationState IS NOT NULL
SET @Tsql = @Tsql + 'LocationState = ' +'''' + @LocationState + ''''
IF @Director IS NOT NULL
SET @Tsql = @Tsql + 'Director = ' + '''' + @Director + ''''
BEGIN
SET NOCOUNT ON
--PRINT @Tsql --use print during testing & checking
EXECUTE sp_executesql @Tsql
END
Run AS:
[GetMapLocationsByState] 'ca',NULL
[GetMapLocationsByState] NULL,'xyz'
I personally do not prefer to use dynamic SQL within the stored procedure as it must recreate the execution plan each time the SQL is executed. However, in this simple query it may not become an issue. Just as a rule, I try to stay away from dynamic SQL unless it is absolutely necessary to achieve the desired results.
Thanks,
Jared
Jared
CE - Microsoft
June 20, 2011 at 1:03 pm
I personally do not prefer to use dynamic SQL within the stored procedure as it must recreate the execution plan each time the SQL is executed. However, in this simple query it may not become an issue. Just as a rule, I try to stay away from dynamic SQL unless it is absolutely necessary to achieve the desired results.
Might I suggest attempting both types dynamic and other, with the different parameter passed in and look at the actual execution plans ... for all possible combinations of supplied parameter values . You might be surprised. I would expect the plan to locate data by state vs by director .. would be significantly different in either case, dynamic or otherwise.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply