I need some help with the following store proc, something is wrong but I just dont see it.
Thanks
btw I am no expert at sp so something might be complety wrong.
ALTER PROCEDURE dbo.GetSearchByDateRange
(
@strColumnNamenvarchar (50),
@dtDate1 Date,
@dtDate2 Date
)
as
EXEC ('SELECT * FROM Customers WHERE ' + @strColumnName + ' BETWEEN ' + ''' + @dtDate1 + ''' + ' AND ' + ''' + @dtDate2 + '''')
I've been bangging my head against the wall on this one and its probably something really easy but for the love of god, I can't figure it out.
I have the following stored procedure
ALTER PROCEDURE [dbo].[InsertUserProjectPhase]
(
@ProjectPhaseID int,
@ProductionEngineerUserID int,
@QuantityCompleted decimal(18, 2),
@BillableHours decimal(18, 2),
@TotalWorkingHours decimal(18, 2),
@DateUpdated datetime,
@UpdatedByUserID int,
@IsDeleted bit
)
AS
SET NOCOUNT OFF;
INSERT INTO User_Project_Phase
(ProjectPhaseID, ProductionEngineerUserID, QuantityCompleted, BillableHours, TotalWorkingHours, DateUpdated, UpdatedByUserID, IsDeleted)
VALUES (@ProjectPhaseID,@ProductionEngineerUserID,@QuantityCompleted,@BillableHours,@TotalWorkingHours,@DateUpdated,@UpdatedByUserID,@IsDeleted);
SELECT ID FROM User_Project_Phase WHERE (ID = SCOPE_IDENTITY())
How can I call the above stored procedure (from within another stored procedure) AND save get the return value (the ID). I tried the following:
exec @newProjectPhaseID = dbo.InsertProjectPhase @ProjectID=3796,@PhaseID=@PhaseID,@EstimatedQuantity=@EstimatedQuantity,
@EstimatedDuration=@EstimatedDuration,@TotalQuantityCompleted=@TotalQuantityCompleted,
@TotalBilledQuantity=@TotalBilledQuantity,@QuantityType=@QuantityTypeID,@StatusID=@StatusID,
@CreatedByUserID=18569
Where @newProjectPhaseID was declared at the top as int. The numbers you see in there will be replaced with variables. They are there now because I'm executing the sp directly in management studio.
Of course, the above doesn't work. I was hoping that the return value form InsertProjectPhase would be saved in @newProjectPhaseID but I get an error.
Any help will be appreciated. THANKS A MILLION!
-Psion
Hi Guys, I created the following stored proc which calls another stored procs as shown below.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Create Procedure [dbo].[rpt_CompareDB](@tbl_name varchar(400),@DB_name1 varchar(400),@DB_name2 varchar(400))
AS
declare @result1 varchar(8000),
@result2 varchar(8000),
@table_name varchar (400),
@DB1 varchar(400),
@DB2 varchar(400),
@table_final1 varchar(400),
@table_final2 varchar(400),
@DB_detail1 varchar(400),
@DB_detail2 varchar(400)
set @table_name = @tbl_name
set @DB1 = @DB_name1
set @DB2 = @DB_name2
set @DB_detail1 = @DB1 + '.mfgq_live'
set @DB_detail2 = @DB2 + '.mfgq_live'
set @table_final1 = @DB_detail1 + '.dbo.' +@table_name
set @table_final2 = @DB_detail2 + '.dbo.' +@table_name
exec GetColumnNamesString @DB_detail1,@table_name,@result1 output
exec GetColumnNamesString @DB_detail2,@table_name,@result2 output
exec CompareTables @table_final1,@table_final2,@result1,@result2
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
but, when I tried to excute it, I am getting an error.
Here is how I'm trying to exec the stored proc.
execute dbo.rpt_CompareDB
@tbl_name = 'Attribute'
,@DB_name1 = 'Green'
,@DB_name2 = 'devlue02'
FYI, It works fine if you run the query itself instead of executing the stored proc.
Any idea is appreciated
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
I have a set of stored procedures copied into several databases (similar table structures but different data). I would like to avoid maintaining so many versions of the same procedures, so want to put the stored procs in a common database and execute it from there, e.g.
exec common_db..the_procedure
However, when you do that, the procedure executes in the context of common_db, not in the context of the calling proc's database. Is there a way of calling a procedure so that the calling proc's context is used?
I am working on a project to re-code SQL Anywhere to T-SQL (SQL Server 2000) and am encountering many instances where the original code contained a SELECT statement that calls a stored procedure. T-SQL does not allow calling a procedure from a SELECT statement as far as I can see. Does anyone have any good ideas on working around this??
Thanks
HelloI have 2 procedures setup in master database, sp_RebuildIndexesMain andsp_RebuildIndexesSubThe Sub just shows and execute DBCC commands for passed databasecontextsp_RebuildIndexesSub(@listOnly bit=0, @maxfrag Decimal=30.0)This runs fine if I do pubs..sp_RebuildIndexesSubHowever when run thru. the Main proc, I get Incorrect syntax near'pubs'.The main proc isCreate Proc sp_RebuildIndexesMain(@dbName sysname, @listOnly bit=0,@maxFrag Decimal=30.0)AsBeginSet NOCOUNT ONDeclare crDbs CURSOR ForSelect CATALOG_NAME From INFORMATION_SCHEMA.SCHEMATAWhere CATALOG_NAME NOT IN ('tempdb', 'master', 'msdb', 'model','distribution', 'Northwind', 'pubs')And CATALOG_NAME Like @dbNameDeclare @execstr nvarchar(2000)Open crDbsFetch crDbs INTO @dbNameIf (@@FETCH_STATUS<>0) --Then no matching databasesBeginClose crDbsDeallocate CrDbsPrint 'No databases were found that match ''' + @dbName + ''''Return -1EndWhile(@@FETCH_STATUS=0)BeginPrint Char(13) + 'Rebuilding indexes on ' + @dbNamePrint Char(13)Set @execstr = @dbName + '..sp_RebuildIndexesSub 'EXEC sp_executesql @execstr, N'@listOnly bit, @maxFrag Decimal',@listOnly, @maxFragFetch crDbs INTO @dbNameEndClose crDbsDeallocate CrDbsReturn 0EndthanksSunitJoin Bytes!
View 5 Replies View RelatedI am just trying to capture the return code from a stored proc as follows and if I get a 1 I want the SQL Task to follow a failure(red) constrainst workflow and send a SMTP mail task warning the customer. How do I achieve the Exec SQL Task portion of this, i get a strange error message [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".
Using OLEDB connection, I utilize SQL: EXEC ? = dbo.CheckCatLog
EXEC SQL Task Editer settings:
RESULTSET: Single Row
PARAMETER MAPPING: User::giBatchID
DIRECTION: OUTPUT
DATATYPE: LONG
PARAMETER NAME: 0
PS-Not sure if I need my variable giBatchID which is an INT32 but I thought it is a good idea to feed the output into here just in case there is no way that the EXEC SQL TASK can chose the failure constrainst workflow if I get a 1 returned or success constraint workflow if I get a 0 returned from stored proceedure
CREATE PROCEDURE CheckCatLog
@OutSuccess INT
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
DECLARE @RowCountCAT INT
DECLARE @RowCountLOG INT
---these totals should match
SELECT @RowCountCAT = (SELECT Count(*) FROM mydb_Staging.dbo.S_CAT)
SELECT @RowCountLOG = (SELECT Count(*) FROM mydb_Staging.dbo.S_LOG)
--PRINT @RowCountCAT
--PRINT @RowCountLOG
BEGIN
IF @RowCountCAT <> @RowCountLOG
--PRINT 'Volume of jobs from the CAT file does not match volume of jobs from the LOG file'
--RETURN 1
SET @OutSuccess = 1
END
GO
Thanks in advance
Dave
Hi
I'm trying to execute a Proc from DTS Package and it is rasing an error 'Invalid Pointer'.
This Proc is used to get a result set and I want to transfer this result set into a Excel File and that Excel File is to be sent to my client by Email.
Every thing was fine....but can some one suggest why I'am getting this error 'Invalid Pointer'.
:confused:
What command do you use to run a DTS package from a stored procedure....
The XP Copy is not working???
Thanks,
~Lee
I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code.
So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message:
Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages.
(1 row(s) affected)
(1 row(s) affected)
I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution?
Also, Is there a way to trace into a stored procedure through Query Analyzer?
-------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised:
SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId
IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
I am having trouble executing a stored procedure on a remote server. On my
local server, I have a linked server setup as follows:
Server1.abcd.myserver.comSQLServer2005,1563
This works fine on my local server:
Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName
This does not work (Attempting to execute a remote stored proc named 'Data_Add':
Exec [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.Data_Add 1,'Hello Moto'
When I attempt to run the above, I get the following error:
Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'.
No entry found with that name. Make sure that the name is entered correctly.
Could anyone shed some light on what I need to do to get this to work?
Thanks - Amos.
Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
View 3 Replies View RelatedI have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.
View 1 Replies View RelatedI have seen this done by viewing code done by a SQL expert and would like to learn this myself. Does anyone have any examples that might help.
I guess I should state my question to the forum !
Is there a way to call a stored proc from within another stored proc?
Thanks In Advance.
Tony
Hi all,
I have a stored procedure "uspX" that calls another stored procedure "uspY" and I need to retrieve the return value from uspY and use it within uspX. Does anyone know the syntax for this?
Thanks for your help!
Cat
Hi all
I have about 5 stored procedures that, among other things, execute exactly the same SELECT statement
Instead of copying the SELECT statement 5 times, I'd like each stored proc to call a single stored proc that executes the SELECT statement and returns the resultset to the calling stored proc
The SELECT statement in question retrieves a single row from a table containing 10 columns.
Is there a way for a stored proc to call another stored proc and gain access to the resultset of the called stored proc?
I know about stored proc return values and about output parameters, but I think I am looking for something different.
Thanks
I would like to know if the following is possible/permissible:
myCLRstoredproc (or some C# stored proc)
{
//call some T SQL stored procedure spSQL and get the result set here to work with
INSERT INTO #tmpCLR EXECUTE spSQL
}
spSQL
(
INSERT INTO #tmpABC EXECUTE spSQL2
)
spSQL2
(
// some other t-sql stored proc
)
Can we do that? I know that doing this in SQL server would throw (nested EXECUTE not allowed). I dont want to go re-writing the spSQL in C# again, I just want to get whatever spSQL returns and then work with the result set to do row-level computations, thereby avoiding to use cursors in spSQL.
Hi: I would like to know if I invoke a stored procedure using "EXEC [database].[user].[StoredProcedure] param1, param2..." is just like to use the ADO.NET objects: SqlCommand myCommand = new SqlCommand("StoredProcedure", myConnection);myCommand.CommandType = CommandType.StoredProcedure;myCommand.Parameters.Add("@param1", param1);myCommand.Parameters.Add("@param2", param2); ..... Thanks
View 3 Replies View RelatedHello
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
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
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
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,
I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.
View 3 Replies View RelatedI 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?
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.
Hi,
Can anyone tell me how to pass parameters from one exec sql task to other ?... (I used stored proc in 1st exec sql task) and passed input parameter (default value set using a variable A) and stored the output parameter value in another variable B.
In the 2nd exec sql task , I passed the output param ( value of B) and doing insert into table xyz...
I get errors (in passing int and string values) . I tried using ole-db as well as ado.net.
Kindly give sample example.
Thanks,
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
Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?
How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.
In this line, @BaseName varchar(50) is polulated by a cursor that queries a table for names of other databases. In this first example it works as predicted:
EXEC('SELECT COUNT (IdPartition) FROM '+@BaseName+'..SAVESET SS LEFT OUTER JOIN SavesetStore SSS ON SS.SavesetIdentity = SSS.SavesetIdentity WHERE [IdPartition] = 0 AND StoreIdentifier IS NULL')
If I create this as an SP (I want the output into another table)
CREATE PROCEDURE GetPArtitionItems @BaseName varchar(50),@IdPartition int, @PartitionItems int OUTPUT
AS
SELECT COUNT (IdPartition) FROM ['+@BaseName+']..SAVESETSS LEFT OUTER JOIN SavesetStore SSS ON SS.SavesetIdentity = SSS.SavesetIdentityWHERE [IdPartition] = @IdPartition AND StoreIdentifier IS NULL
GODeclare @PartitionItems intEXECUTE GetPartitionItems 'evmailboxstore1',0,@PartitionItems OUTPUT --EvMailboxStore1 is another table in the same database.
I get: Server: Msg 208, Level 16, State 1, Procedure GetPArtitionItems, Line 7Invalid object name ''+@BaseName+'..SAVESET'
In this case the value is not passed into the @baseName-variable. What do I do wrong?
Thanks in advance - Tim Kuhnell
Hello,
Does anybody know, how can I execute stored procedures that are on MSSQL Server 2000 from Oracle9i SP ? Can you show me an examle ?
Thanks for any help.
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 RelatedHi 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.