Report Error Using Stored Procedure With Exec Sp_executesql
Feb 13, 2008
Hi,
Our report is working fine with data loaded from a stored procedure (#1) that contains a fairly simple Select statement. We need the same report to work with a dataset loaded from a stored procedure (#2) that uses 'Exec sp_executesql @queryString'. Unfortunately, attempts to call the latter cause an error in the report. From everything that I've read, there should be no difference between datasets created using either method. Any ideas what could be getting in the way of the latter?
I have doublechecked that the dynamic query is returning a valid dataset and that all the columns are in the same format as sp #1. The designer shows the dataset and the report with the data loaded, but the live system produces an error.
Any help is much appreciated.
Debbie
View 4 Replies
ADVERTISEMENT
Nov 6, 2007
I am building a stored procedure that changes based on the data that is available to the query. See below.
The query fails on line 24, I have the line highlighted like this.
Can anyone point out any problems with the sql?
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the error...
Msg 8114, Level 16, State 5, Procedure sp_SearchCandidatesAdvanced, Line 24
Error converting data type varchar to numeric.
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the exec point...
EXEC [dbo].[sp_SearchCandidatesAdvanced]
@LicenseType = 4,
@PositionType = 4,
@BeginAvailableDate = '10/10/2006',
@EndAvailableDate = '10/31/2007',
@EmployerLatitude = 29.346675,
@EmployerLongitude = -89.42251,
@Radius = 50
GO
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the STORED PROCEDURE...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_SearchCandidatesAdvanced]
@LicenseType int = 0,
@PositionType int = 0,
@BeginAvailableDate DATETIME = NULL,
@EndAvailableDate DATETIME = NULL,
@EmployerLatitude DECIMAL(10, 6),
@EmployerLongitude DECIMAL(10, 6),
@Radius INT
AS
SET NOCOUNT ON
DECLARE @v_SQL NVARCHAR(2000)
DECLARE @v_RadiusMath NVARCHAR(1000)
DECLARE @earthRadius DECIMAL(10, 6)
SET @earthRadius = 3963.191
-- SET @EmployerLatitude = 29.346675
-- SET @EmployerLongitude = -89.42251
-- SET @radius = 50
SET @v_RadiusMath = 'ACOS((SIN(PI() * ' + @EmployerLatitude + ' / 180 ) * SIN(PI() * p.CurrentLatitude / 180)) + (COS(PI() * ' + @EmployerLatitude + ' / 180) * COS(PI() * p.CurrentLatitude / 180) * COS(PI()* p.CurrentLongitude / 180 - PI() * ' + @EmployerLongitude + ' / 180))) * ' + @earthRadius
SELECT @v_SQL = 'SELECT p.*, p.CurrentLatitude, p.CurrentLongitude, ' +
'Round(' + @v_RadiusMath + ', 0) AS Distance ' +
'FROM ProfileTable_1 p INNER JOIN CandidateSchedule c on p.UserId = c.UserId ' +
'WHERE ' + @v_RadiusMath + ' <= ' + @Radius
IF @LicenseType <> 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND LicenseTypeId = ' + @LicenseType
END
IF @PositionType <> 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND Position = ' + @PositionType
END
IF LEN(@BeginAvailableDate) > 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND Date BETWEEN ' + @BeginAvailableDate + ' AND ' + @EndAvailableDate
END
--SELECT @v_SQL = @v_SQL + 'ORDER BY CandidateSubscriptionEmployerId DESC, CandidateFavoritesEmployerId DESC, Distance'
PRINT(@v_SQL)
EXEC(@v_SQL)
-----------------------------------------------------------------------------------------------------------------
View 4 Replies
View Related
Jul 23, 2005
This is a odd problem where a bad plan was chosen again and again, butthen not.Using the profiler, I identified an application-issued statement thatperformed poorly. It took this form:exec sp_executesql N'SELECT col1, col2 FROM t1 WHERE (t2= @Parm1)',N'@Parm1 int', @Parm1 = 8609t2 is a foreign key column, and is indexed.I took the statement into query analyzer and executed it there. Thequery plan showed that it was doing a scan of the primary key index,which is clustered. That's a bad choice.I then fiddled with it to see what would result in a good plan.1) I changed it to hard code the query value (but with the parmdefinition still in place. )It performed well, using the correct index.Here's how it looked.exec sp_executesql N'SELECT cbord.cbo1013p_AZItemElement.AZEl_Intid AS[Oid], cbord.cbo1013p_AZItemElement.incomplete_flag AS [IsIncomplete],cbord.cbo1013p_AZItemElement.traceflag AS [IsTraceAmount],cbord.cbo1013p_AZItemElement.standardqty AS [StandardAmount],cbord.cbo1013p_AZItemElement.Uitem_intid AS [NutritionItemOid],cbord.cbo1013p_AZItemElement.AZeldef_intid AS [AnalysisElementOid] FROMcbord.cbo1013p_AZItemElement WHERE (Uitem_intid= 8609)', N'@Parm1 int',@Parm1 = 8609After doing this, re-executing the original form still gave badresults.2) I restored the use of the parm, but removed the 'exec' from thestart.It performed well.After that (surprise!) it also performed well in the original form.What's going on here?
View 3 Replies
View Related
Dec 7, 2007
Hi, I just had a quick question about this code and if I know what it is doing: Is it putting what is in variable @x18 into @Lost_Alumni?
Declare @Lost_Alumin datetime
Set @ParmDef_Ls = '@x18 DateTime OUTPUT, '
EXEC sp_executeSQL @ParmDef_Ls,
@x18 = @Lost_Alumni OUTPUT,
thanks,
View 5 Replies
View Related
Dec 24, 2007
Hi,
I Have a stored procedure named pPatientFindByID that return one patient record from the Client.Patient table. The stored procedure has one parameter @patientID uniqueidentifier. i have set the NOCOUNT ON before running the SQL statement and making it OFF after it finishes execution. The table has a primary key ( clustered ) on patientID. The table has 66840 records in it. The index IS 0 (ZERO) percent fragmented and 3 level depth.
The stored proc returnes null or 1 record maximum. My problem is that the stored procedure takes about 2268 microsecond (2 millisecond) on average. while i execute the same SQL using sp_executeSQL it takes about 710 microsecond (< 1 millisecond). That is 3 time faster than stored procedure.
The SET OPTION are same for both the statement.
To troubleshoot the problem i have enabled the trace and captured the events. there were no recompile for both the stored proc and sp_executeSQL and not even the statement level recompilation.
I am trying to figure out why my stored procedure is taking longer than sp_executesql. Both the (stored proc and sp_executeSQL) has the same execution plan.
This is affecting our application performance. Does anyone know about this or have experience the same problem ever? your help would be greatly appriciated.
Data
1. Stored Procedure
/****** Object: StoredProcedure [dbo].[pPatientFindByID] Script Date: 12/24/2007 11:19:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[pPatientFindByID]
@patientID uniqueidentifier
WITH EXECUTE AS OWNER
AS
BEGIN
SET NOCOUNT ON
SELECT
[patient].[AVINumber] AS 'AVINumber',
[patient].[BirthDate] AS 'BirthDate',
[patient].[BreedID] AS 'BreedID',
[patient].[ChangeDate] AS 'ChangeDate',
[patient].[ChangeWorkstationID] AS 'ChangeWorkstationID',
[patient].[ClientID] AS 'ClientID',
[patient].[Color] AS 'Color',
[patient].[County] AS 'County',
[patient].[CountyRabiesTagNumber] AS 'CountyRabiesTagNumber',
[patient].[CreateDate] AS 'CreateDate',
[patient].[CreateWorkstationID] AS 'CreateWorkstationID',
[patient].[DeathDate] AS 'DeathDate',
[patient].[DeathReasonCode] AS 'DeathReasonCode',
[patient].[FirstVisitDate] AS 'FirstVisitDate',
[patient].[HasAllergy] AS 'HasAllergy',
[patient].[HasBitesOrScratch] AS 'HasBitesOrScratch',
[patient].[HasChronicCondition] AS 'HasChronicCondition',
[patient].[HasDeclinedPreventiveCare] AS 'HasDeclinedPreventiveCare',
[patient].[HasMedicalAllergy] AS 'HasMedicalAllergy',
[patient].[HospitalID] AS 'HospitalID',
[patient].[IsBites] AS 'IsBites',
[patient].[IsBitesInFear] AS 'IsBitesInFear',
[patient].[IsClinicalStudy] AS 'IsClinicalStudy',
[patient].[IsLitters] AS 'IsLitters',
[patient].[IsMixBreed] AS 'IsMixBreed',
[patient].[IsScratches] AS 'IsScratches',
[patient].[LastVisitDate] AS 'LastVisitDate',
[patient].[ManufacturerNameOfAVIChip] AS 'ManufacturerNameOfAVIChip',
[patient].[ManufacturerNameOfMicrochip] AS 'ManufacturerNameOfMicrochip',
[patient].[MicrochipNumber] AS 'MicrochipNumber',
[patient].[Name] AS 'Name',
[patient].[Note] AS 'Note',
[patient].[PatientID] AS 'PatientID',
[patient].[RabiesTagNumber] AS 'RabiesTagNumber',
[patient].[SexCode] AS 'SexCode',
[patient].[SpeciesID] AS 'SpeciesID',
[patient].[Status] AS 'Status',
[patient].[SystemLog] AS 'SystemLog',
[patient].[Weight] AS 'Weight',
[patient].[WellnessplanLevel] AS 'WellnessplanLevel',
[patient].[WellnessplanValue] AS 'WellnessplanValue'
FROM
[Client].[Patient] [patient]
WHERE
[PatientID] = @patientID
SET NOCOUNT OFF
END
2. sp_executeSQL
set nocount on
execute
sp_executeSQL N'
SELECT [patient].[AVINumber]
,[patient].[BirthDate]
,[patient].[BreedID]
, [patient].[ChangeDate]
, [patient].[ChangeWorkstationID]
, [patient].[ClientID]
, [patient].[Color]
, [patient].[County]
, [patient].[CountyRabiesTagNumber]
, [patient].[CreateDate]
, [patient].[CreateWorkstationID]
, [patient].[DeathDate]
, [patient].[DeathReasonCode]
, [patient].[FirstVisitDate]
, [patient].[HasAllergy]
, [patient].[HasBitesOrScratch]
, [patient].[HasChronicCondition]
, [patient].[HasDeclinedPreventiveCare]
, [patient].[HasMedicalAllergy]
, [patient].[HospitalID]
, [patient].[IsBites]
, [patient].[IsBitesInFear]
, [patient].[IsClinicalStudy]
, [patient].[IsLitters]
, [patient].[IsMixBreed]
, [patient].[IsScratches]
, [patient].[LastVisitDate]
, [patient].[ManufacturerNameOfAVIChip]
, [patient].[ManufacturerNameOfMicrochip]
, [patient].[MicrochipNumber]
, [patient].[Name]
, [patient].[Note]
, [patient].[PatientID]
, [patient].[RabiesTagNumber]
, [patient].[SexCode]
, [patient].[SpeciesID]
, [patient].[Status]
, [patient].[SystemLog]
, [patient].[Weight]
, [patient].[WellnessplanLevel]
, [patient].[WellnessplanValue]
FROM [Client].[Patient] [patient] WHERE [PatientID] = @patientID
',N'@patientID uniqueidentifier'
,'B1ABC1EA-D4AB-DB11-921E-00087434402F'
set nocount off
3- Trace Data
Name Event TextData Duration
SQL:BatchCompleted 12 exec dbo.pPatientFindByID @patientID='B1ABC1EA-D4AB-DB11-921E- 00087434402F' 2268
SQL:BatchCompleted 12 set nocount on execute sp_executeSQL N' SELECT [patient].[AVINumber] 710
DBA
View 8 Replies
View Related
Sep 11, 2001
I need to do some dynamic execs based on SQL strings as variables taken from a table.
I need the best performance. In testing, I'm finding that the string I am executing is taking 20 times as long to run from within an Exec or sp_executesql vs running the same string from directly within query analyzer. Of course, there's some overehead with Exec, and we're talking milliseconds (30ms in query analyzer vs 600 in an Exec), but this still seems like a lot of overhead. I assume both statements still need to be compiled and an execution plan created. Is this difference in time normal?
I ask because I'm debating about whether to execute this process (which may need to run 9000 times a second) in a stored procedure or using an executable running separately on the server. I assume an executable's performance will approximate running the same query from query analyzer.
Here's the query:
Select GroupNodeNumber, SB.* From (Select Distinct GroupNodeNumber from #QASData) RD Cross Join (SELECT * From SQL_QAS_ServiceFailure WHERE QASEventID=99077223) SB Where (GroupNodeNumber=10 and (((VerbalPOD=-1) or (Canceled=-1) or ((DeliveryRequirement) = -1) or
(DateDiff(n,GetDate(),DeliveryRequirement)-(DestTimeAdjustment)>240) or (InvoiceType<>0)))) or
(GroupNodeNumber=20 and ((Canceled=0) and (NOT (DeliveryRequirement) = -1) and (DateDiff(n,GetDate(),DeliveryRequirement)-(DestTimeAdjustment)>240) and (VerbalPOD=0) and (InvoiceType=0))) or
(GroupNodeNumber=30 and ((Canceled=0) and (NOT (DeliveryRequirement) = -1) and (DateDiff(n,GetDate(),DeliveryRequirement)-(DestTimeAdjustment)>120) and (DateDiff(n,GetDate(),DeliveryRequirement)-(DestTimeAdjustment)<=240) and (VerbalPOD=0) and (InvoiceType=0))) or
(GroupNodeNumber=40 and ((Canceled=0) and (InvoiceType=0) and (VerbalPOD=0) and (NOT (DeliveryRequirement) = -1) and (DateDiff(n,GetDate(),DeliveryRequirement)-(DestTimeAdjustment)<=120))) or
(GroupNodeNumber=50 and ((ShipmentStatus='POD') and (VerbalPOD=0) and (InvoiceType=0)))
View 1 Replies
View Related
May 1, 2008
Hi everybody,
I have this problem I have been struggling with for more than an hour now, hoping for some help.
Using SQL Server 2005.
DECLARE @IPAddress nvarchar(15)
SET @IPAddress = '127.0.0.1'
DECLARE @UA nvarchar(300)
SET @UA = 'MSIE'
DECLARE @UserID int
SET @UserID = 123
EXEC sp_executesql N'User_LogonLog',N'@UserID int, @IPAddress nvarchar(15), @UA nvarchar(300)',@UserID,@IPAddress,@UA
It keeps given me the following error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'User_LogonLog'.
In my head it should be perfectly valid syntax. I have also read Mr. Sommarskog's nice guide http://www.sommarskog.se/dynamic_sql.html, but no luck either..
I have tried to remove datatypes in parameters declaration, the Unicdode N' and so... nu luck.
If I run the following (without sp_executesql) there is no problem, which indicates the User_LogonLog procedure is work perfectly fine.
EXEC User_LogonLog @UserID,@IPAddress,@UA
What am I missing?
View 6 Replies
View Related
Mar 26, 2008
Hi There
Ok i have a piece of test ddl sql that is written to a varchar(max) column. Entered by a user.
GO's and missing semi colons seem to break it, if you try to execute it with EXEC or sp_executesql, however the sql can be executed on Management Studio as is, so how can i execute it as is successfully?
In a nutshell i retreive the DDL sql from the column into a nvarchar(max) variable called @SQL , and i need to execute it.
I have tried:
EXEC(@SQL) and sp_executesql @SQL, both return the error , incorrect syntax near 'GO'.
The problem is obviously i have to have the go statements, in order to create some fo the ddl objects correctly. But EXEC and sp_executesql do not like that. I also found that semi colons are required after every statement.
The sql is as follows:
--===============================================================================
--DDL script
CREATE LOGIN TEST_LOGIN WITH PASSWORD = 'Whatever!@(!';
CREATE USER TEST_USER FROM LOGIN TEST_LOGIN;
CREATE TABLE TEST_TABLE (Column1 int NULL);
GRANT INSERT ON TEST_TABLE TO TEST_USER;
CREATE CLUSTERED INDEX TEST_INDEX ON TEST_TABLE (Column1);
GO
CREATE PROCEDURE TEST_PROCEDURES
AS
SELECT GETDATE();
GO
CREATE VIEW TEST_VIEW
AS
SELECT * FROM TEST_TABLE;
GO
--ALTER DDL
ALTER VIEW TEST_VIEW
AS
SELECT GETDATE() AS 'DATE';
GO
ALTER PROCEDURE TEST_PROCEDURES
AS
SELECT * FROM TEST_TABLE;
GO
--DROP DDL
DROP INDEX TEST_TABLE.TEST_INDEX;
DROP TABLE TEST_TABLE;
DROP VIEW TEST_VIEW;
DROP USER TEST_USER;
DROP LOGIN TEST_LOGIN;
DROP PROCEDURE TEST_PROCEDURES;
--===============================================================================
View 5 Replies
View Related
Nov 5, 2007
Hello
Which is faster :
to write a a big stored procedure with if conditions, or to separate them and call them using exec??
i.e:
if @id=1 insert into ....else if @id=2 update...-----------------------orif @id=1 exec InsertProcedureelse if @id=2 exec UpdateProcedurePlease help
View 8 Replies
View Related
Jan 10, 2006
Hi,
Hope someone can help me out here - I'm trying to design a data flow in SSIS which relies in part on a stored procedure as a data source - I'm running it through OLE DB.
Sure Exec MystoredProc works fine on preview and on parsing, but it refuses to acknowledge any of the columns, when I go to Edit-->Columns everything is blank.
Just out of interest - the reason I am using a stored procedure is because I dump the data into a temp table and then amend a couple of the columns to make it the same as my other database (for example where len(field) = 6 then field = '0000' + field).
Possibly I'm better off taking the raw data through the OLE connection and then transforming it through SSIS, but my gut feeling is I should minimise what I'm dumping into SSIS and offload the processing onto the local DB. Any thoughts?
Thanks
Rich
View 6 Replies
View Related
Aug 20, 2007
Hi,
I'm new to SSIS and SQL Server 2005 and this is now driving me very mad!!
I have an OLE DB Command in my data flow task that I want to update a table with. I have looked round this forum and on Google and just can not find a solution or what I am doing wrong. So any help would be great!
The ole db command calls a stored procedure with two input variables:
exec stp_updedgrsholds status, temp_cr_num
from debugging the ssis it says it has updated 4 rows and also from doing a data view, the data it is updating seems all correct.
but nothing gets updated in the database.
If I call the stored procedure the following way
exec stp_updedgrsholds 'C', 87
It updates fine! I have tried a number of different way with @ symbols and assignment p_status = @status
but nothing seems to work.
Any ideas are much appreciated.
Ninder Bassi
View 4 Replies
View Related
May 23, 2007
hi,
how do I exec stored procedure that accept parameter and return a single value?
here is example of report
stu_id = ******
stu_name = ****
subject | marks
aa****** | call sp_mark and return student mark for that particular student id and subject
bb****** | call sp_mark and return student mark for that particular student id and subject
cc****** | call sp_mark and return student mark for that particular student id and subject
thks,
View 3 Replies
View Related
Apr 29, 2004
Hi there
i have a stored procedure like this:
CREATE PROCEDURE SP_Main
AS
SET NOCOUNT ON
BEGIN TRANSACTION
exec SP_Sub_One output
exec SP_Sub_Two output
IF @@ERROR = 0
BEGIN
-- Success. Commit the transaction.
Commit Tran
END
ELSE
Rollback Tran
return
GO
now the problem is, when i execute the stored procedure's inside the main stored procedure, and these sub sp's has an error on it, the main stored procedure doesnt rollback the transation.
now i can put the "begin transation" in the two sub stored procedure's. The problem is
what if the first "SP_Sub_One" executed successfully, and there was error in "SP_Sub_Two"
now the "SP_Sub_One" has been executed and i cant rollback it... the error occured in the
"SP_Sub_Two" stored procedure ... so it wont run ... so there will be error in the data
how can i make a mian "BEGIN TRANSACTION" , that even it include the execution of stored procedure in it.
Thanks in advance
Mahmoud Manasrah
View 1 Replies
View Related
Mar 8, 2007
Hello,
I'm using SQL service Broker 2005 with ASP.NET 2.0 in order to use the sql cache dependency.
everything works fine...
I have only a doubt regarding a query that is constantly executed on mu db ( i can see it be means of the SQL Profiler)
The query is:
exec sp_executesql N'BEGIN CONVERSATION TIMER ("'') TIMEOUT = 120; WAITFOR(RECEIVE TOP (1) message_type_name, conversation_handle,
cast(message_body AS XML) as message_body from [SqlQueryNotificationService-GUID]), TIMEOUT @p2;',N'@p2 int',@p2=60000
The web application is not running from a browser ...
It this ok or I forget to clean/reset something from my web application and/or sql server?
Thank you
Marina B.
View 3 Replies
View Related
Oct 2, 2007
I have this code in a stored procedure: DECLARE @SQLString VarChar(200)
SET @SQLString = 'SELECT ' + @LookupField + ' FROM ' + @DBTable + ' WHERE (' + @IDField + ' = ''' + @IDValue + ''')'
Exec (@SQLString)
it works fine - with just one issue - I must grant select permission on the table.
Is there a way to do this WITHOUT granting the select permissions?
View 7 Replies
View Related
Mar 12, 2008
I Have a problem When I execute a stored procedure from query analyzer
(Exec storedname @parameter1='', @Parameter2='') it take 7 min. and I stop running
If I copy stored procedure , past it in Query analyzer and declare parameters
it take 3 sec.
View 3 Replies
View Related
Feb 12, 2008
Hello,
If I grant execute permissions on stored procedures in a database and the proc in turn creates tables in the DB, and if the user is not a db_owner, will the procedure be allowed to create those tables? or will the stored procs fail?
Thanks
Arun
View 4 Replies
View Related
Sep 20, 2005
Is there a way to execute a stored procedure and have it move on without waiting for a response from the stored procedure. I am trying to create a button on a webpage that will execute a stored procedure but the procedure takes to long to run and my page times out. Instead I would like the button to start the procedure and the webpage look at a table of data. When the table of data is empty then I will know the stored procedure is complete. Is this possible?
View 1 Replies
View Related
Dec 19, 2007
Hi All,
I'm unable to run the report the report with multi-valued parameter using the below StoredProcedure as dataset:
CREATE PROCEDURE spprodsales
@productid int
AS
select SalesOrderID,OrderQty,UnitPrice,ProductID
FROM Sales.SalesOrderDetail
Where ProductID IN (@productid)
RETURN
And when I'm replacing this dataset to a query as below I'm able to run the report with multiple values selected for productid parameter :
select SalesOrderID,OrderQty,UnitPrice,ProductID
FROM Sales.SalesOrderDetail
Where ProductID IN (@productid)
So, can anyone please help me out possibly using the same stored procedure as above.
Thanks,
Kripa
View 5 Replies
View Related
Feb 21, 2004
Hi everybody, I would like to know if it's possible to execute a stored procedure, passing it parameters, using not CommandType.StoredProcedure value of sqlcommand, but CommandType.Text.
I tried to use this:
sqlCmd.CommandType = CommandType.Text
sqlCmd.Parameters.Add(sqlPar)
sqlCmd.ExecuteNonQuery()
With this sql command:
"exec sp ..."
I wasn't able to make it to work, and I don't know if it's possible.
Another question:
if it's not possible, how can I pass a Null value to stored procedure?
This code:
sqlPar = new SqlParameter("@id", SqlDbType.Int)
sqlPar.Direction = ParameterDirection.Output
cmd.Parameters.Add(sqlPar)
sqlPar = new SqlParameter("@parent_id", DBNull)
cmd.Parameters.Add(sqlPar)
doesn't work, 'cause I get this error:
BC30684: 'DBNull' is a type and cannot be used as an expression.
How can I solve this?
Bye and thanks in advance.
P.S. I would prefer first method to call a stored procedure ('cause I could call it with 'exec sp null' sql command, solving the other problem), but obviusly if it's possible...=)
Sorry for grammatical mistakes.
View 9 Replies
View Related
Oct 1, 2007
Hi All,
I have created a dynamic SQL STATEMENT , but the result of the execution of that statement matters to me. within stored procedure.
Note: If run the statement using EXEC() command, the result gets displayed on the SQL Editor.
But I DO NOT KNOW HOW to Capture that value.
Any idea how to capture as I would like capture the result and stored it in a table.
Thank you.
--Israr
View 4 Replies
View Related
Feb 8, 2008
Hi
I've used a temporary table in the stored procedure
I've created it as DECLARE @Temp TABLE (id INT)
in select clause I've used this temp table through the joins..
But I've also used a varchar variable to store my criteria...
which I'm building in the stored procedure
so inorder to use it in the where clause I used
EXEC ('SELECT ....................
FROM @Temp T
LEFT OUTER JOIN ...
LEFT OUTER JOIN ...
WHERE ' + @Criteria )
Unfortunately this is not working.
Giving the errror
Must declare the variable '@TempT'.
I had to use Temporary table using #, which I feel is really waste of memory...
Is there a way by which I can use my Table variable in the EXEC statement.
Thanks In advance
View 4 Replies
View Related
Jul 23, 2005
Hello,Is it possible to EXEC stored procedure from a query?I want to execute stored procedure for every line of SELECT resulttable.I guess it's possible with cursors, but maybe it's possible to make iteasier.Give an example, please.Thank you in advance.Hubert
View 2 Replies
View Related
Apr 26, 2008
I have a temporary table with multiple records and a Stored Procedure requiring a value. In a Stored Procedure, I want to loop through records in the table and use the value from each record read as input to another Stored Procedure. How do I do this?
View 7 Replies
View Related
May 14, 2008
Hello,
I have a stored procedure where I run an insert statement. I want to knwo if it is possible to do it using a variable for the table name (either in-line or with an EXEC statement without building a string first and executing that string. See examples of what I am talking about in both cases below:
I want to be able to do this (with or without the EXEC) :
------------------------------------------------------------------------------------
DECLARE @NewTableNameOut as varchar(100)
Set @NewTableNameOut = 'TableToInsertInto'
EXEC(
Insert Into @NewTableNameOut
Select * From tableToSelectFrom
)
------------------------------------------------------------------------------------
I can not do the above because it says I need to declare/set the @NewTableNameOut variable (assuming it is only looking at this for the specific insert statement and not at the variable I set earlier in the stored procedure.
I can do it like this by creating a string with the variable built into the string and then executing the string but I want to know if I can do it like I have listed above.
------------------------------------------------------------------------------------
DECLARE @NewTableNameOut as varchar(100)
Set @NewTableNameOut = 'TableToInsertInto'
EXEC(
'Insert Into ' + @NewTableNameOut + ' ' +
'Select * From tableToSelectFrom'
)
------------------------------------------------------------------------------------
It is not an issue for my simple example above but I have some rather large queries that I am building and I want to run as described above without having to build it into a string.
Is this possible at all?
If you need more info please let me know.
View 1 Replies
View Related
Jul 10, 2006
I have the following stored proc that is providing the following error - Server: Msg 156, Level 15, State 1, Line 79[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'ORDER'.
The sp works fine if the DateOfBirth elements are removed. I think it is something to do with the way in which the date variable is incorporated into the string that will be executed by the EXEC command but I'm unsure as to what is wrong.
CREATE PROCEDURE dbo.GetPersonsByName (@FirstName varchar(50)=NULL, @FamilyName varchar(50)=NULL, @DateOfBirth datetime=NULL)
AS
EXEC ('SELECT PersonId, Title, FirstName, FamilyName , AltFamilyName, Sex, DateOfBirth, Age, DateOfDeath, CauseOfDeath, Height, Weight, ABO, RhD, Comments, LocalIdNo, NHSNo, CHINo, Hospital, HospitalNo, AltHospital, AltHospitalNo, EthnicGroup, Citizenship, NHSEntitlement, HomePhoneNo, WorkPhoneNo, MobilePhoneNo, CreatedBy, DateCreated, UpdatedBy, DateLastUpdated
FROM vw_GetPersons WHERE FirstName LIKE ''%' + @FirstName + '%'' AND FamilyName LIKE ''%' + @FamilyName + '%'' AND DateOfBirth = '+ @DateOfBirth +' ORDER BY FamilyName, FirstName')
GO
View 12 Replies
View Related
Jan 17, 2008
Hi all,
In my SQL Server Management Studio Express (SSMSE), I executed the following sql code suuccessfully:
--insertNewRocord.sql--
USE shcDB
GO
CREATE PROC sp_insertNewRecord @procPersonID int,
@procFirstName nvarchar(20),
@procLastName nvarchar(20),
@procAddress nvarchar(50),
@procCity nvarchar(20),
@procState nvarchar(20),
@procZipCode nvarchar(20),
@procEmail nvarchar(50)
AS INSERT INTO MyFriends
VALUES (@procPersonID, @procFirstName, @procLastName, @procAddress,
@procCity, @procState, @procZipCode, @procEmail)
GO
EXEC sp_insertNewRecord 7, 'Peter', 'Wang', '678 Old St', 'Detroit',
'Michigon', '67899', 'PeterWang@yahoo.com'
GO
=======================================================================
Now, I want to insert a new record into the dbo.Friends table of my shcDB by executing the following T-SQL and Visual Basic 2005 codes that are programmed in a VB2005 Express project "CallshcDBspWithAdoNet":
--Form1.vb--
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form1
Public Sub InsertNewFriend()
Dim connectionString As String = "Integrated Security-SSPI;Persist Security Info=False;" + _
"Initial Catalog=shcDB;Data Source=.SQLEXPRESS"
Dim connection As SqlConnection = New SqlConnection(connectionString)
connection.Open()
Try
Dim command As SqlCommand = New SqlCommand("sp_InsertNewRecord", connection)
command.CommandType = CommandType.StoredProcedure
EXEC sp_insertNewRecord 6, 'Craig', 'Utley', '5577 Baltimore Ave',
'Ellicott City', 'MD', '21045', 'CraigUtley@yahoo.com'
Console.WriteLine("Row inserted: " + _
command.ExecuteNonQuery().ToString)
Catch ex As Exception
Console.WriteLine(ex.Message)
Throw
Finally
connection.Close()
End Try
End Sub
End Class
===========================================================
I ran the above project in VB 2005 Express and I got the following 5 errors:
1. Name 'EXEC' is not declared (in Line 16 of Form1.vb)
2. Method arguments must be enclosed in parentheses (in Line 16 of Form1.vb)
3. Name 'sd-insertNewRecord' is not declared. (in Line 16 of Form1.vb)
4.Comma, ')', or a valid expression continuation expected (in Line 16 of Form1.vb)
5. Expression expected (in Line 16 of Form1.vb)
============================================================
I know that "EXEC sp_insertNewRecord 6, 'Craig', 'Utley', '5577 Baltimore Ave',
'Ellicott City', 'MD', '21045', 'CraigUtley@yahoo.com' "in Line 16 of Form1.vb is grossly in error.
But I am new in doing the programming of T-SQL in VB 2005 Express and I am not able to change it.
Please help and advise me how to correct these problems.
Thanks in advance,
Scott Chang
View 22 Replies
View Related
Feb 25, 2004
What is error here when i declare cursor ?
declare curQueryVehicleHave cursor for
exec sp_executesql @strQueryVehicleHave
@strQueryVehicleHave this string contain a query
View 2 Replies
View Related
Jun 16, 2008
I have the following code which works, but every time i try to put a procedure around it to pass in the 3 variables I set in this code manually it bombs on me.
~SQL~
USE [myDb]
-- variable declarations
DECLARE @sql nvarchar(max)
DECLARE @params nvarchar(max)
DECLARE@tableName nvarchar(50)
DECLARE@personId nvarchar(36)
DECLARE@encId nvarchar(36)
DECLARE@prov_reviewOUT nvarchar(1)
DECLARE@prov_opened_templateOUT nvarchar(1)
--TEST:: add input values
set @tableName = 'master_im_'
set @personId = '9B9192AF-97E1-4FD6-8279-362E4944ECAC'
set @encId = '63570609-529B-4488-BBBF-E039A8F10D92'
-- build SQL String for dynamic SQL query
SELECT @sql = N'SELECT @prov_reviewRET = provider_review, @prov_opened_templateRET = prov_opened_template
FROM dbo.' + quotename(@tableName) + ' WHERE person_id = @personId AND enc_id = @encId'
-- build parameters for sp_executesql
SELECT @params = N'@personId nvarchar(36), ' +
N'@encId nvarchar(36), ' +
N'@prov_reviewRET nvarchar(1) OUTPUT, ' +
N'@prov_opened_templateRET nvarchar(1) OUTPUT '
-- execute dynamic sql string
EXEC sp_executesql
@sql,
@params,
@personId,
@encId,@prov_reviewRET = @prov_reviewOUT OUTPUT,
@prov_opened_templateRET = @prov_opened_templateOUT OUTPUT
select @prov_reviewOUT, @prov_opened_templateOUT
~endSQL~
works fine every time...
but when i do this:
~sql~
create procedure svsp_test
@tableName nvarchar(50),
@personId nvarchar(36),
@encId nvarchar(36),
@prov_reviewOUT nvarchar(1) OUTPUT,
@prov_opened_templateOUT nvarchar(1) OUTPUT
AS
-- variable declarations
DECLARE @sql nvarchar(max)
DECLARE @params nvarchar(max)
-- build SQL String for dynamic SQL query
SELECT @sql = N'SELECT @prov_reviewRET = provider_review, @prov_opened_templateRET = prov_opened_template
FROM dbo.' + quotename(@tableName) + ' WHERE person_id = @personId AND enc_id = @encId'
-- build parameters for sp_executesql
SELECT @params = N'@personId nvarchar(36), ' +
N'@encId nvarchar(36), ' +
N'@prov_reviewRET nvarchar(1) OUTPUT, ' +
N'@prov_opened_templateRET nvarchar(1) OUTPUT '
-- execute dynamic sql string
EXEC sp_executesql
@sql,
@params,
@personId,
@encId,
@prov_reviewRET = @prov_reviewOUT OUTPUT,
@prov_opened_templateRET = @prov_opened_templateOUT OUTPUT
~endsql~
and then run the proc like this:
declare @prov_reviewOUT nvarchar(1), @prov_opened_templateOUT nvarchar(1)
exec svsp_test 'master_im_', '9B9192AF-97E1-4FD6-8279-362E4944ECAC', '63570609-529B-4488-BBBF-E039A8F10D92', null, null
select @prov_reviewOUT, @prov_opened_templateOUT
I always get null returned. If i put a select statement into the stored proc create code as the last line, it shows the correct values, but trying to read them from the sp in the create procedure statement always returns null. Can someone help me figure out what I'm doing incorrectly please?
Thanks!
View 3 Replies
View Related
Jul 23, 2005
Greetings All, currentley there is a heated discussion in my place ofwork over which method is better/more efficient for simple selects.Background:1.) Simple Application that uses sql server for backened db.2.) The application is only inserting and selecting data from the db.3.) The developers want to use sp_executesql for simple selects andthe dba's want to use a stored proc.[color=blue]>From my reading it seems that sp_executesql has a bit of overhead with[/color]it and it is not as efficient as stored procs.I would appreciate anyone's input on which would be better for simplerepetitive inserts to the db: Stored Proc, or sp_executesql?Regards, TFD.
View 3 Replies
View Related
Jan 19, 2007
Can someone help me with this issue? I am trying to update a record using a sp. The db table has an identity column. I seem to have set up everything correctly for Gridview and SqlDataSource but have no clue where my additional, phanton arguments are being generated. If I specify a custom statement rather than the stored procedure in the Data Source configuration wizard I have no problem. But if I use a stored procedure I keep getting the error "Procedure or function <sp name> has too many arguments specified." But thing is, I didn't specify too many parameters, I specified exactly the number of parameters there are. I read through some posts and saw that the gridview datakey fields are automatically passed as parameters, but when I eliminate the ID parameter from the sp, from the SqlDataSource parameters list, or from both (ID is the datakey field for the gridview) and pray that .net somehow knows which record to update -- I still get the error. I'd like a simple solution, please, as I'm really new to this. What is wrong with this picture? Thank you very much for any light you can shed on this.
View 9 Replies
View Related
May 15, 2008
i have created the folowing function but keep geting an error.
Only functions and extended stored procedures can be executed from within a function.
Why am i getting this error!
Create Function myDateAdd
(@buildd nvarchar(4), @avdate as nvarchar(25))
Returns nvarchar(25)
as
Begin
declare @ret nvarchar(25)
declare @sqlval as nvarchar(3000)
set @sqlval = 'select ''@ret'' = max(realday) from (
select top '+ @buildd +' realday from v_caltable where realday >= '''+ @avdate +''' and prod = 1 )a'
execute sp_executesql @sqlval
return @ret
end
View 3 Replies
View Related
Feb 21, 2007
I cannot execute a package by using Execute Package task.
I supplied sa credentials to connection manager, and it shows the list of Packages on SQL Server but when running the task it says
Error 0xC0202009 while preparing to load the package. An OLE DB error has occurred. Error code: 0x%1!8.8X!.
Any clue ?
Thanks,
Fahad
View 1 Replies
View Related
