SP_EXECUTESQL

Question

SP_EXECUTESQL

ravikanth232

Mr or Mrs. 500

Points: 543
More actions
April 30, 2010 at 6:36 am

#146764

Hi,
I am using SP_EXECUTESQL in a procedure to insert data in to temp table. but it is giving error
Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Code is:
USE [DB_159120_geehr]
GO
/****** Object: StoredProcedure [dbo].[GetCompliances] Script Date: 04/29/2010 19:46:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetCompliances]
-- Add the parameters for the stored procedure here
@WHERE_CLAUSE varchar(4000) = null,
@IssueNumber int = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
if(@WHERE_CLAUSE is not null)
begin
Declare @SqlStr varchar(max);
Create Table #ComplianceTemp
( IssueCode varchar(30),
IssueID Int,
CommentsVarchar(300),
CreatedByVarchar(50),
StatusNameVarchar(20),
RaisedByIDVarchar(20),
IssueRelatedToID int,
RelatedToTypeName Varchar(20),
IssueReportedOnID Int,
IssueReportedOnValue varchar(150),
IssueNumber int,
IssueReportedByDetails varchar(150),
IssueReportedByID int,
IssueReportedFromDetail varchar(150),
IssueDetails varchar(500),
IssueTypeName varchar(30),
Notesvarchar(500),
UpdatedDatevarchar(30),
UpdatedBy varchar(50),
RequestedDate varchar(30)
)
set @SqlStr = N'Insert into #ComplianceTemp
(IssueCode,IssueID, Comments, CreatedBy, StatusName, RaisedByID, IssueRelatedToID ,
RelatedToTypeName , IssueReportedOnID ,IssueReportedOnValue , IssueNumber ,IssueReportedByDetails ,
IssueReportedByID , IssueReportedFromDetail ,
IssueDetails , IssueTypeName , Notes , UpdatedDate , UpdatedBy,RequestedDate )
select * from
(select C.IssueCode,C.IssueID,C.Comments,C.CreatedBy,MS.StatusName,C.RaisedByID,C.IssueRelatedToID,
MR.RelatedToTypeName,C.IssueReportedOnID,C.IssueReportedOnValue ,C.IssueNumber,C.IssueReportedByDetails,
C.IssueReportedByID,MRP.IssueReportedFromDetail,MI.IssueDetails ,MT.IssueTypeName ,c.Notes,
convert(varchar(10),c.UpdatedDate,101) UpdatedDate ,
c.UpdatedBy,convert(varchar(10),C.CreatedDate,101)
From ComplianceIssues C,
MasterIssueRelatedTo MR,
dbo.MasterIssueReportedFrom MRP,
dbo.MasterIssueStatus MS,
dbo.MasterIssue MI,
dbo.MasterIssueType MT
where c.IssueRelatedToID=MR.RelatedToTypeID and
c.IssueReportedByID = MRP.IssueReportedFromIDand
c.IssueID = MI.IssueID and
c.StatusID=MS.StatusID and
MI.IssueTypeID=MT.IssueTypeId ) tbl where '+ @WHERE_CLAUSE
print @SqlStr
EXECUTE SP_EXECUTESQL @SqlStr;
select * from #ComplianceTemp c
order by c.IssueNumber desc;
end
else
begin
select C.IssueCode,
C.IssueID,
C.Comments,
C.CreatedBy,
MS.StatusName,
C.RaisedByID,
C.IssueRelatedToID,
MR.RelatedToTypeName,
C.IssueReportedOnID,
C.IssueReportedOnValue ,
C.IssueNumber,
C.IssueReportedByDetails as IssueReportedByDetails,
--C.IssueReportedByDetails,
C.IssueReportedByID,
MRP.IssueReportedFromDetail,
MI.IssueDetails ,
MT.IssueTypeName ,
c.Notes,
convert(varchar(10),c.UpdatedDate,101) UpdatedDate ,
c.UpdatedBy,convert(varchar(10),C.CreatedDate,101) as RequestedDate
From ComplianceIssues C,
MasterIssueRelatedTo MR,
dbo.MasterIssueReportedFrom MRP,
dbo.MasterIssueStatus MS,
dbo.MasterIssue MI,
dbo.MasterIssueType MT
where c.IssueRelatedToID=MR.RelatedToTypeID and
c.IssueReportedByID = MRP.IssueReportedFromIDand
c.IssueID = MI.IssueID and
c.StatusID=MS.StatusID and
MI.IssueTypeID=MT.IssueTypeId and
(@IssueNumber is null or IssueNumber = @IssueNumber) and c.IssueID<>4 order by IssueNumber desc
end
IF NOT (@IssueNumber IS NULL)
BEGIN
select
c.IssueNumber,c.LegendID,C.LegendDetails,l.LegendName
from dbo.ComplianceIssueLegend c ,dbo.MasterLegend l
where c.Legendid = l.legendid AND C.ISSUENUMBER =@IssueNumber ;
END
ELSE
BEGIN
select
c.IssueNumber,c.LegendID,C.LegendDetails,l.LegendName
from dbo.ComplianceIssueLegend c ,dbo.MasterLegend l
where c.Legendid = l.legendid AND C.ISSUENUMBER =0 ;
END
END

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

ravikanth232 (4/30/2010)
I am using SP_EXECUTESQL in a procedure to insert data in to temp table. but it is giving error
Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1

post your complete error

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

Also post

what below statement @sqlstr prints ?

print @SqlStr
EXECUTE SP_EXECUTESQL @SqlStr;

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

ravikanth232 Mr or Mrs. 500 Points: 543 More actions · Answer 3

Error Message is

Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1

Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

ravikanth232 (4/30/2010)
Error Message is
Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

post the SP run sample code which you are using

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

ravikanth232 Mr or Mrs. 500 Points: 543 More actions · Answer 5

DECLARE@return_value int

EXEC@return_value = [dbo].[GetCompliances]

@WHERE_CLAUSE = N'RelatedToTypeName LIKE 'Mem%'',

@IssueNumber = NULL

SELECT'Return Value' = @return_value

Exception:

Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1

Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 6

Change this...

Declare @SqlStr varchar(max);

... to this...

Declare @SqlStr Nvarchar(max);

... and try again.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ravikanth232 Mr or Mrs. 500 Points: 543 More actions · Answer 7

ravikanth232

Mr or Mrs. 500

Points: 543

April 30, 2010 at 8:26 pm

#1160848

Thanq...

that helped me...procedure is working not.

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 8

ravikanth232 (4/30/2010)
Thanq...
that helped me...procedure is working not.

You're welcome. I'm assuming that you really meant "procedure is working now".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ravikanth232 Mr or Mrs. 500 Points: 543 More actions · Answer 9

am sorry.....

i misspelled...

procedure is working now....