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

Help With TSQL Stored Procedure - Error-Exec Point-Procedure Code

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

Exec Sp_executesql Vs. Sp_executesql And Performance

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

EXEC Sp_executesql

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

Sp_executeSQL Perform Faster Than Stored Procedure, Why?

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

Exec Or Sp_Executesql Performance

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

EXEC Sp_executesql Does Not Work

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

Cannot Execute DDL Sql Batach Via EXEC Or Sp_executesql ???

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

Exec Stored Procedure

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

Exec Stored Procedure

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

Exec Stored Procedure

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

How To Exec A Stored Procedure

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

Exec Twp Stored Procedure In A Main Stored Procedure

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

From The Sql Profiler Constantly Displays Exec Sp_executesql N'BEGIN CONVERSATION TIMER ... It That Ok?

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

Stored Procedure Exec An SQL Statement

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

Timeout While Exec Stored Procedure

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

Exec Stored Procedure Permission

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

Exec A Stored Procedure And Not Wait For Response

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

Error With Multi-Valued Report Parameter Using Stored Procedure

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

Execute Stored Procedure (with Parameters) With An Exec Command

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

How To Capture The ResultSet Of EXEC Command In Stored Procedure

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

Can't TABLE Variable Be Used In EXEC Statement In Stored Procedure

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

EXEC Stored Procedure For Every Line Of SELECT Result Table - How?

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

In Stored Procedure How To Loop Through Rows In Table And Pass Parameter To EXEC SP

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

Stored Procedure Using A Declared Variable In Insert Query (inline Or Using EXEC)

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

SQL Error When Using EXEC In Stored Proc

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

T-SQL And Visual Basic 2005 Codes That Execute A User-Defined Stored Procedure In Management Studio:How To Declare EXEC &&amp; Sp?

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

How Assign Value To Cursor Using Sp_executesql Procedure

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

Sp_executesql Within A Stored Proc?

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

Sp_executesql Vs. Stored Proc.

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

Gridview / SqlDataSource Error - Procedure Or Function &<stored Procedure Name&> Has Too Many Arguments Specified.

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

Only Functions And Extended Stored Procedures Can Be Executed From Within A Function. Sp_executesql Is A Extended Stored Prod

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

Exec Pkg Task: Error 0xC0202009 While Preparing To Load The Package. An OLE DB Error Has Occurred. Error Code: 0x%1!8.8X!.

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







Copyrights 2005-15 www.BigResource.com, All rights reserved