Solved

sql server query in stored procedure

Posted on 2016-08-19
3
36 Views
Last Modified: 2016-08-19
I have this query in a stored procedure:

is it possible for me to pass a parameter to it of some sort so that it could do a slightly different piece of sql.
the difference in this case would be not including the first left join?



USE [Dictionary]
GO
/****** Object:  StoredProcedure [dbo].[usp_GetBulkDescStrings]    Script Date: 19/08/2016 17:05:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_GetBulkDescStrings]

AS
BEGIN
select strshort, StrShortTag, [dbo].[TblWords].ClientName, [dbo].TblVehCat.VehCategory
FROM [dbo].[TblWords] LEFT JOIN [TblWordTags] ON [TblWords].ClientCodeWordPosition = [TblWordTags].ClientCodeWordPosition LEFT JOIN [TblVehCat] ON [TblWords].ClientCode = [TblVehCat].ClientCode
WHERE [TblWordTags].ClientCodeWordPosition Is Not Null
group by strshort, StrShortTag, [dbo].[TblWords].ClientName, [dbo].TblVehCat.VehCategory
order by ClientName
END

Open in new window

0
Comment
Question by:PeterBaileyUk
  • 2
3 Comments
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 500 total points
ID: 41762655
sure it is possible. check this:
alter PROCEDURE dbo.usp_GetBulkDescStrings
	@paramWithoutJoin int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	IF @paramWithoutJoin = 0 
	BEGIN 
		select strshort, StrShortTag, [dbo].[TblWords].ClientName, [dbo].TblVehCat.VehCategory
		FROM [dbo].[TblWords] 
		LEFT JOIN [TblWordTags] ON [TblWords].ClientCodeWordPosition = [TblWordTags].ClientCodeWordPosition 
		LEFT JOIN [TblVehCat] ON [TblWords].ClientCode = [TblVehCat].ClientCode
		WHERE [TblWordTags].ClientCodeWordPosition Is Not Null
		group by strshort, StrShortTag, [dbo].[TblWords].ClientName, [dbo].TblVehCat.VehCategory
		order by ClientName
	END 
	ELSE
    BEGIN
--your other query
		select *
		FROM [dbo].[TblWords] 
    END 
END

Open in new window

0
 

Author Comment

by:PeterBaileyUk
ID: 41762709
ok so i just pass a zero or 1


ok
0
 

Author Closing Comment

by:PeterBaileyUk
ID: 41762711
thank you
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question