Solved

Stored procedure

Posted on 2016-09-26
4
38 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
[X]
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
  • 2
  • 2
4 Comments
 
LVL 52

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 52

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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.

738 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