Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Stored procedure

Posted on 2016-09-26
4
35 Views
Last Modified: 2016-09-26
Hello,

Can't get this Sp working. Any suggestions?


CREATE PROCEDURE  [dbo].[Tel_WordReport]
(
     @tableName varchar(100) = null,
	 @ColumnName1 varchar(100) = null,
	 @Value varchar(100) = null
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;  	
	
	   declare @SQL varchar(500) = null
	  -- NONCLUSTERED INDEX [NIX__UNQ__UID_]
	   DECLARE @Year_Ref smallint
		SET @Year_Ref = (SELECT [Year_Ref] FROM YEAR_LOOKUP  WHERE [Year_no]= YEAR(GETDATE()))

	



	SET @SQL = 'SELECT Date,Ref, Comments,SentTo,SentBy,Regarding'
		
		SET @SQL = @SQL + ' FROM  [' + @tableName + '] '   + ' WHERE  [' + @ColumnName1 + ']  = ''' +   '' +  @Value +''''+ ' AND  [Year_Ref] =''' + '23 '+''''
	
	
	
	  SET @SQL = @SQL  	
	  
	  EXEC (@SQL)	
   
	
END

Open in new window

0
Comment
Question by:RIAS
  • 2
  • 2
4 Comments
 
LVL 51

Expert Comment

by:Ryan Chong
ID: 41815502
the Year_Ref was hard coded with space value?

>>AND  [Year_Ref] =''' + '23 '+''''

from your SP, you will have query like this:
SELECT Date,Ref, Comments,SentTo,SentBy,Regarding FROM  [yourTable]  WHERE  [yourField]  = 'yourValue' AND  [Year_Ref] ='23 '

Open in new window

make sure it's a valid statement for your DB.
0
 

Author Comment

by:RIAS
ID: 41815510
Thanks Ryan,
How can I avoid hard coded value and  use
  DECLARE @Year_Ref smallint
            SET @Year_Ref = (SELECT [Year_Ref] FROM YEAR_LOOKUP  WHERE [Year_no]= YEAR(GETDATE()))
0
 
LVL 51

Accepted Solution

by:
Ryan Chong earned 500 total points
ID: 41815514
quick try:
ALTER PROCEDURE  [dbo].[Tel_WordReport]
(
     @tableName varchar(100) = null,
	 @ColumnName1 varchar(100) = null,
	 @Value varchar(100) = null
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;  	
	
	   declare @SQL varchar(500) = null
	  -- NONCLUSTERED INDEX [NIX__UNQ__UID_]
	   DECLARE @Year_Ref smallint
		SET @Year_Ref = (SELECT [Year_Ref] FROM YEAR_LOOKUP  WHERE [Year_no]= YEAR(GETDATE()))

	SET @SQL = 'SELECT Date,Ref, Comments,SentTo,SentBy,Regarding'
		
		SET @SQL = @SQL + ' FROM  [' + @tableName + '] '   + ' WHERE  [' + @ColumnName1 + ']  = ''' +   '' +  @Value +''''+ ' AND  [Year_Ref] =' + convert(varchar,@Year_Ref)
	
	  SET @SQL = @SQL  	
	  
	  EXEC (@SQL)	
	
END

Open in new window

1
 

Author Closing Comment

by:RIAS
ID: 41815522
Cheers mate!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

828 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