I'm getting an error trying to add an sp that selects top @varname...I'm surprised since variables are typically allowed in t-sql selects.
Is this by design or am I doing something wrong? Do I have to build the select dynamically to accomplish this?
I just want to do this (shortened example):
Select hours from GetBaseStoredProc '10/01/2004' Where ReasonId = 1
where GetBaseStoredProc is a stored procedure that takes a date parameter and contains a select sql statement like:
select reasonId, hours from my table where mydate = @myDate
(in reality, it's a much larger and more complicated statement, just using this as an example)
I guess I'm treating my storedProc like a view. If worse comes to worse, I suppose I could create a view (would rather not), but I'm wondering if what I want to do is possible, and I'm just not using the right syntax.
Many thanks - (a former Oracle dev)
i have a vb app that is retriving data from an sql db using ado. i have a qry save in my db. i want to select from this qry where x in (1,2,3,4). i don't know how many values i will be putting into my where in statment. it could be one value or 100 values. below is my code. what i would like to do is pass one paramater to my stored proc and then break it up and use it in my select.
my desired result is as follows
-----------------------------------------------
declare cnt int
declare TempFulLString nvarchar(5000)
declare TempStyleFID int
TempFulLString = @str_TempString
select * from QryPicking_Slip_Fill_Listview1 where stylefid in (
While TempFulLString <> ''
begin
cnt = InStr(1, TempFulLString, ',')
TempStyleFID = Left(TempFulLString, (cnt))
TempFulLString = Right(TempFulLString, (Len(TempFulLString) - cnt))
TempStyleFID + ','
end
)
-----------------------------------------------
I'm trying to call a stored proc with parameters without using EXEC statement and with only using a SELECT statement. I have the stored proc and need to call it from a 3rd party application which provides an interface to SQL server and does not support EXEC statements, but only SELECT statements. Is there a way this can be done?
Any enlightenment is appreciated.
hi all
i have a simple problem that i cant get through.
i am writing a stored procedure and i want to make this select statement
SELECT TOP 100 PERCENT dbo.tPA00175.chrJobNumber, dbo.tPA20802.dteDocumentDate, dbo.tPA00002.chrPhaseName, dbo.tPA00007.chrEmployeeNumber,
dbo.tPA20802.numActualQuantity, dbo.tPA20802.numChargeOutRate, dbo.tPA20801.numTotalCharges, dbo.tPA00125.numQTYInvoiced
FROM dbo.tPA00125 INNER JOIN
dbo.tPA00007 ON dbo.tPA00125.intEmployeeKey = dbo.tPA00007.intEmployeeKey INNER JOIN
dbo.tPA20802 ON dbo.tPA00125.intJobLineKey = dbo.tPA20802.intJobLineKey INNER JOIN
dbo.tPA20801 ON dbo.tPA20802.intTimesheetKey = dbo.tPA20801.intTimesheetKey INNER JOIN
dbo.tPA00002 ON dbo.tPA00125.intPhaseKey = dbo.tPA00002.intPhaseKey INNER JOIN
dbo.tPA00175 ON dbo.tPA00125.intJobKey = dbo.tPA00175.intJobKey
WHERE (dbo.tPA00125.numQTYInvoiced = 0)
ORDER BY dbo.tPA00175.chrJobNumber DESC
how would i go about setting my variables, if at all possible, from the values im calling for in the select statement?
is there another way of doing it rather than this
set @Phase=(select dbo.tPA00002.chrPhaseName
FROM dbo.tPA00125 INNER JOIN
dbo.tPA00007 ON dbo.tPA00125.intEmployeeKey = dbo.tPA00007.intEmployeeKey INNER JOIN
dbo.tPA20802 ON dbo.tPA00125.intJobLineKey = dbo.tPA20802.intJobLineKey INNER JOIN
dbo.tPA20801 ON dbo.tPA20802.intTimesheetKey = dbo.tPA20801.intTimesheetKey INNER JOIN
dbo.tPA00002 ON dbo.tPA00125.intPhaseKey = dbo.tPA00002.intPhaseKey INNER JOIN
dbo.tPA00175 ON dbo.tPA00125.intJobKey = dbo.tPA00175.intJobKey
WHERE (dbo.tPA00125.numQTYInvoiced = 0))
set @Resource=(select dbo.tPA00007.chrEmployeeName
FROM dbo.tPA00125 INNER JOIN
dbo.tPA00007 ON dbo.tPA00125.intEmployeeKey = dbo.tPA00007.intEmployeeKey INNER JOIN
dbo.tPA20802 ON dbo.tPA00125.intJobLineKey = dbo.tPA20802.intJobLineKey INNER JOIN
dbo.tPA20801 ON dbo.tPA20802.intTimesheetKey = dbo.tPA20801.intTimesheetKey INNER JOIN
dbo.tPA00002 ON dbo.tPA00125.intPhaseKey = dbo.tPA00002.intPhaseKey INNER JOIN
dbo.tPA00175 ON dbo.tPA00125.intJobKey = dbo.tPA00175.intJobKey
WHERE (dbo.tPA00125.numQTYInvoiced = 0))
set @hours=(select dbo.tPA20802.numActualQuantity
FROM dbo.tPA00125 INNER JOIN
dbo.tPA00007 ON dbo.tPA00125.intEmployeeKey = dbo.tPA00007.intEmployeeKey INNER JOIN
dbo.tPA20802 ON dbo.tPA00125.intJobLineKey = dbo.tPA20802.intJobLineKey INNER JOIN
dbo.tPA20801 ON dbo.tPA20802.intTimesheetKey = dbo.tPA20801.intTimesheetKey INNER JOIN
dbo.tPA00002 ON dbo.tPA00125.intPhaseKey = dbo.tPA00002.intPhaseKey INNER JOIN
dbo.tPA00175 ON dbo.tPA00125.intJobKey = dbo.tPA00175.intJobKey
WHERE (dbo.tPA00125.numQTYInvoiced = 0))
set @rate=(select dbo.tPA20802.numChargeOutRate
FROM dbo.tPA00125 INNER JOIN
dbo.tPA00007 ON dbo.tPA00125.intEmployeeKey = dbo.tPA00007.intEmployeeKey INNER JOIN
dbo.tPA20802 ON dbo.tPA00125.intJobLineKey = dbo.tPA20802.intJobLineKey INNER JOIN
dbo.tPA20801 ON dbo.tPA20802.intTimesheetKey = dbo.tPA20801.intTimesheetKey INNER JOIN
dbo.tPA00002 ON dbo.tPA00125.intPhaseKey = dbo.tPA00002.intPhaseKey INNER JOIN
dbo.tPA00175 ON dbo.tPA00125.intJobKey = dbo.tPA00175.intJobKey
WHERE (dbo.tPA00125.numQTYInvoiced = 0))
set @amount=(select dbo.tPA20801.numTotalCharges
FROM dbo.tPA00125 INNER JOIN
dbo.tPA00007 ON dbo.tPA00125.intEmployeeKey = dbo.tPA00007.intEmployeeKey INNER JOIN
dbo.tPA20802 ON dbo.tPA00125.intJobLineKey = dbo.tPA20802.intJobLineKey INNER JOIN
dbo.tPA20801 ON dbo.tPA20802.intTimesheetKey = dbo.tPA20801.intTimesheetKey INNER JOIN
dbo.tPA00002 ON dbo.tPA00125.intPhaseKey = dbo.tPA00002.intPhaseKey INNER JOIN
dbo.tPA00175 ON dbo.tPA00125.intJobKey = dbo.tPA00175.intJobKey
WHERE (dbo.tPA00125.numQTYInvoiced = 0))
set @date=(select dteDocumentDate
FROM dbo.tPA00125 INNER JOIN
dbo.tPA00007 ON dbo.tPA00125.intEmployeeKey = dbo.tPA00007.intEmployeeKey INNER JOIN
dbo.tPA20802 ON dbo.tPA00125.intJobLineKey = dbo.tPA20802.intJobLineKey INNER JOIN
dbo.tPA20801 ON dbo.tPA20802.intTimesheetKey = dbo.tPA20801.intTimesheetKey INNER JOIN
dbo.tPA00002 ON dbo.tPA00125.intPhaseKey = dbo.tPA00002.intPhaseKey INNER JOIN
dbo.tPA00175 ON dbo.tPA00125.intJobKey = dbo.tPA00175.intJobKey
WHERE (dbo.tPA00125.numQTYInvoiced = 0))
SET @invDate=(select(getdate()))
thanks alot
tibor
Hey,
I create a Select Statement in stored proc and I have printed the variable and it has the correct Select statement. My problem is now that I have the string I want how do I run it.
Thanks
Hi, i'm an SQL newbie. I'm trying to figure out if there's an easy way to take a single field record set from a SELECT statement and then do an INSERT using that record set, all in one single Stored Procedure.
Here's what i tried to do, but this returns an error "The name "phonenumber" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.".
The common field in both SELECT and INSERT is phonenumber.
quote:PROCEDURE [dbo].[usp_select_and_insert]
@name varchar(20),
AS
SELECT phonenumber FROM USERLIST where OWNERNAME LIKE @name
INSERT INTO LOGLOG (destination,content) values(phonenumber,'hello world');
GO
Hope that one of you can be kind enough to give me some guidance. Appreciate in advance. :)
I am using SQL sever 2k and C#.
There is a stored-procedure that it has multiple select statements as returned result set. How can I use SqlCommand.ExecuteReader to get all result set?
What if the multiple select statements is " FOR XML", how can I set all xml using ExecuteXmlReader?
I tried to use ExecuteReader or ExecuteXmlReader, but seems that I can only get back the result set of the first select statement, all others are messed up.
stored procedure example: NorthWind database:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Create PROCEDURE dbo.getShippersAndEmployeesXML
AS
select * from Shippers for xml auto, elements
select * from Employees for xml auto, elements
RETURN @@ERROR
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
C# code example:
//set connect, build sqlcommand etc
XmlTextReader reader = (XmlTextReader)command.ExecuteXmlReader();
StringBuilder sb = new StringBuilder();
while(reader.Read()) sb.Append(reader.ReadOuterXml());
Thanks for your help.
Using SQL Server 7 I am trying to modify an existing stored proc and make it more flexible. The below example represents the first part of that proc. The temp table that it should return is then used by another part of the proc (this query represents the foundation of my procedure). I need to figure a way to change the SQL Select statement, choosing between C.CONTRACTCODE and CB.EMPLOYERCODE on the fly. The query below will run but no records are returned. I am starting to believe/understand that I may not be able to use the @option variable the way I am currently.
I've tried creating two SQL statements, assigning them as strings to the @option variable, and using EXEC(@option). The only problem with this is that my temp table (#savingsdata1) goes out of scope as soon as the EXEC command is complete (which means I can not utilize the results for the rest of the procedure). Does anyone know how I can modify my procedure and incorporate the flexibility I've described?
Thanks,
Oliver
CREATE PROCEDURE test
@ContractCode varchar(10),
@dtFrom datetime,
@dtTo datetime,
@Umbrella int
AS
declare @option varchar(900)
if @umbrella = 0
set @option = 'c.contractcode'
else
set @option = 'cb.employercode'
select
c.claimsno,
c.attenddoctor,
c.patientcode,
p.sex,
cb.employercode
into #SavingsData1
from claimsa c inner join Patient p
on c.patientcode = p.patientcode
inner join claimsb cb on c.claimsno = cb.claimno
where
@option = @ContractCode and c.dateentered between @dtFrom and @dtTo
and c.claimsno like 'P%' and p.sex in('M','F') and c.attenddoctor <> 'ZZZZ'
select * from #SavingsData1
Hi
Is it possible to SELECT a field from another database which is on the same server from within a stored procedure?
thanks
If I run this statement in Query Analyzer, it properly returns 1for my testing table. But if I put the statement into a storedprocedure, the stored procedure returns NULL. What am I doingwrong? I suspect it may be related to how I defined the parametersfor the stored procedure. Perhaps my definition of TableName andColumnName don't match what COLUMNPROPERTY and OBJECT_ID expect toreceive, but I don't know where to look for the function declarationsfor those. Any pointers would be appreciated.Select statement:SELECT COLUMNPROPERTY(OBJECT_ID('Table1'), 'TestID', 'IsIdentity') ASIsIdentityTable definition:CREATE TABLE [dbo].[Table1] ([TestID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]Stored Procedure definition:CREATE PROCEDURE spTest(@TableName varchar,@ColumnName varchar)AS SELECT COLUMNPROPERTY(OBJECT_ID(@TableName), @ColumnName,'IsIdentity') AS IsIdentity
View 2 Replies View RelatedHiI'm not sure what the best approach for this is:I have a stored procedure which I would like to use to return severaloutput values instead of returning a recordset.CREATE PROCEDURE Test (@param1 int, @param2 int OUTPUT, @param3 intOUTPUT) ASSELECT field2, field3 FROM Table WHERE field1 = @param1I would like to return @param2 as field2 and @param3 as field3How do I do this without using SELECT multiple times?THanks in advanceSam
View 6 Replies View RelatedI have a stored proc that I'm using to run a report. It works fine, but currently I'm using a parameter that is a single selection from a dropdown. I'd like to use multi select, but have not been able to get it to work.
In the data tab I'm currently using "text" for command type and :
Code Snippet
declare @sql nvarchar(2000)
set @sql = '
EXEC [Monitor] '' + @p_OfferStatus + '''
exec sp_executesql @sql, N'@p_OfferStatus VARCHAR(100)', @p_OfferStatus = @p_OfferStatus
when I run this in the data tab, it works fine, returning data, but when I try to preview it it tells me there are syntax errors. Anyone know the correct way to use multi selects with stored procs?
Hi All,
I think what am trying to do is quite basic.
I have 3 paramaters@value1, @value2,@value3 being passed into a stored
proc and each of these parameters can be blank. If one of them is blank
and the rest of them have some valid values, then I should just exclude
the column check for the value that is blank.
For e.g if all my parameters being passed are non- empty then I would
do this
select * from tblName
where column1 like @value1 and column2 like @value2 and column3 like
@value3
else if I have one of the parameter being passed as empty, I should
ignore that parameter like
if@value1 is empty then my sql should be
select * from tblName
where column2 like @value2 and column3 like @value3
I don't want to do a dyanmic sql because of rights and security issue.
I want it through a stored procedure only.
Also, all the three columns can have null values in the table.
Please let me know what is the best possible way to do this. Thanks in
advance !.
.noscripthide
{display:none;}
.noscriptinline
{display:inline;}
.noscriptblock
{display:block;}
.scripthide
{display:none;}
.scriptinline
{display:inline;}
.scriptblock
{display:block;}
.script12hide
{display:none;}
.script12inline
{display:inline;}
.script12block
{display:block;}
.lnav
{position:absolute;}
.lnavch
{margin-left:23.0ex;}
.script13hide
{display:none;}
.script13inline
{display:inline;}
.script13block
{display:block;}
.hide
{display:none;}
.hide_ie
{;}
.hide_ie
{display:none;}
img
{border:0;}
img
{border-color:#0000a0;}
input.ck
{margin-left:-2px;}
input.bt, button.bt
{padding:0 .4em 0 .4em;width:auto;overflow:visible;}
input.bt, button.bt
{width:1px;}
.fixed_width
{font-family:fixed-width, monospace;font-size:90%;}
a:visited
{color:#551a8b;}
a:active
{color:#f00;}
.inheritcolor a
{color:inherit;}
.minmaxwie
{width:100%;}
* html .minmaxwie
{;}
.fl:visited
{color:#551a8b;}
.fl:active
{color:#f00;}
.fl:link
{color:#7777CC;}
.z
{display:none;}
.on:active
{color:#f00000;}
.don:active
{color:#f00000;}
.mbody
{margin-top:4px;}
body,td,input,textarea,select
{font-family:arial,sans-serif;}
body,td
{font-size:83%;}
input,textarea,select
{font-size:100%;}
form
{margin:0;}
.tick
{font-family:webdings;text-decoration:none !important;}
.qr
{width:100%;padding:4px;font-family:arial,sans-serif;}
.nu
{text-decoration:none;}
.gt
{border-collapse:collapse;}
.gt td
{padding:.3em 4px;border-right:1px solid #ffcc33;}
.gm td
{padding:.3em 1em .3em 0px;}
.bnk
{border:1px solid #ffcc33;}
.bnk td
{border-right-width:0px !important;}
.sel td.seltd
{padding:4px 4px 4px 4px;border:1px solid #ffcc33;border-right:none;font-weight:bold;}
p.b
{margin-bottom:1.5em;margin-top:.3em;}
.adb, .adbrnav
{border-left:1px solid #fff4c2;}
.msgdate
{color:#676767;}
.md
{color:#555555;}
.st
{margin-left:-1px;}
.nb
{white-space:nowrap;}
.np
{padding:0px;}
.p
{font-weight:bold;}
.mo
{margin:.5em 0 0 0;}
.oa
{padding:2px .5em;}
.sbox
{margin-top:1em;margin-bottom:1em;}
button a:link
{text-decoration:none;color:black;}
button a:hover
{text-decoration:none;color:black;}
.b
{font-weight:bold;}
.fontsize0
{font-size:78%;}
.fontsize1
{font-size:87%;}
.fontsize2
{font-size:96%;}
.fontsize_25
{font-size:100%;}
.fontsize3
{font-size:108%;}
.fontsize4
{font-size:120%;}
.fontsize5
{font-size:133%;}
.fontsize6
{font-size:150%;}
.fontsize7
{font-size:150%;}
.cv
{width:100%;}
.lk
{color:#0000CC;text-decoration:underline;cursor:pointer;}
.nl
{padding:5px 0 2px 5px;}
.tsh
{border-top:1px solid #ffcc33;}
.tlsh
{border-top:1px solid #ffcc33;}
.blsh
{border-bottom:1px solid #ffcc33;}
.bsh
{border-bottom:1px solid #ffcc33;}
.lsh, .tlsh, .blsh
{border-left:1px solid #ffcc33;}
.lnav
{left:9px;width:23.0ex;overflow:hidden;}
.lnavch
{;}
.lnavi
{font-size:100%;}
.lnavim
{margin-left:-2px;}
* html .lnav
{left:11px;}
.alertboxout
{margin:5px 15px 0px 10px;clear:left;}
.alertboxin
{clear:left;color:black;background-color:#fad163;font-weight:bold;text-align:center;padding:0px 15px 0px 15px;position:relative;margin:-1px 0px;}
.ctl
{padding-left:2px;}
.mb
{padding:6px 8px 0 5px;}
.exh
{margin:0 0 0 5px;background-color:#e8e8e8;}
.exh div div
{padding-top:4px;}
.thread_star
{padding:0 0 4px 2px;}
* html .thread_star
{padding:0 0 0 2px;}
.blurb_star
{padding:0 0 0 2px;}
* html .blurb_star
{padding:2px 0 0 2px;}
Hi,
I have used several sql queris to generate a report. This queries pull out data from different tables. But sometimes at the same table too.
Basically those are SELECT statements.
I have created stored proc for each SELECT statement. now I'm wondering can I include all SELECT statements in one stored proc and run the report.
If possible, can anyone show me the format?
Thanks
Hi,
Finally found what is causing my .net c# service application to return with Error 18456 (NT Authority/anonymous logon" severity 14 State 11.
It is connecting to a sql server db in another machine and running a stored procedure in that db. I found out that if I remove that two statements that create temp tables (with select INTO), the stored procedure executes without a problem.
Note that this stored procedure updates the tables does not have problems updated tables that are in the DB. I gets upset when creating temp tables though.
I cannot do away with those temp tables as they are needed in the calculation. How can I fix this problem?
Why would creating temp tables remotely cause this error? What am I missing in my set up. My service application connects with the integrated security = true. My service process installer has the account set to "User". What am I missing.
Please please help.
Ahrvie
I am new to web services and as a DBA I only have limited .net experience. Our development team is creating a web services interface for our new IVR phone system, and we've decided on a solution that will send each of the phone call parameters from our database to the phone system in an XML based on line number and extension. I've found that Http Endpoints in sql server will be a perfect solution for exactly that and I won't need any .net to develop it. Thats the good news. There are, however, other requirements for our phone system to communicate back to our database. All of the examples I've seen for endpoints use a stored procedure performing a select statement.
Is it possible to create an endpoint that references a stored procedure performing an insert or update? To be more specific, can I create an endpoint that references a stored procedure that has parameters? If so, how do I pass those parameters through a web service request? Is it as simple as adding &variable="value" to the url?
If so I could develop this entire solution from the database side and not involve our .net programmer!
Another alternative: the phone system does have it own database in sql server. I'm not sure if our phone system provided can or will do this, but if they are willing to work with this, maybe a service broker can fit into this solution.
I look forward to any response. Thanks!
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.
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 RelatedHello 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.
Hi Peeps
I have a SP that returns xml
I have writen another stored proc in which I want to do something like this:Select FieldOne, FieldTwo, ( exec sp_that_returns_xml ( @a, @b) ), FieldThree from TableName
But it seems that I cant call the proc from within a select.
I have also tried
declare @v xml
set @v = exec sp_that_returns_xml ( @a, @b)
But this again doesn't work
I have tried changing the statements syntax i.e. brackets and no brackets etc...,
The only way Ive got it to work is to create a temp table, insert the result from the xml proc into it and then set @v as a select from the temp table -
Which to be frank is god awful way to do it.
Any and all help appreciated.
Kal
I'm trying to write a Stored Proc to Insert records into a table in SQL Server 2000. The fields in the records to be inserted are from another table and from Parameters. I can't seem to figure out the syntax for this.
I created a test in MS Access and it loooks like this:
INSERT INTO PatientTripRegionCountry_Temp ( CountryID, RegionID, Country, PatientTripID )
SELECT Country.CountryID, Country.RegionID, Country.Country, 2 AS PatientTripID
FROM Country
This works great in Access but not in SQL Server. In SQL Server 2 = @PatientTripID
ANY SUGGESTIONS ON HOW TO HANDLE THIS?
I try to get the TOP of the query but SQL do not allow me to do so. Is there a way to do this.
thanx
===============
Incorrect syntax near '@NbrItems'. Line 14
===============
create procedure NewsList
@ModuleID int,
@NbrItems int
as
if @NbrItems = 0
select * from TblNews where ModuleID = @ModuleID order by CreationDate DESC
else
select top @NbrItems * from TblNews where ModuleID = @ModuleID order by CreationDate DESC
GO
I would like to write 1 proc that can take additional criteria if its sent in. An example is:
select HA.PriceId, HA.VendorPackageId from Criteria HA Inner Join
(
select VendorPackageId from ValidVendorPackages
where Vendor = @VENDOR
and Sitecode = @SITECODE
and PackageType = @PACKAGETYPE
)HB on HA.VendorPackageId = HB.VendorPackageId
and CriteriaId in
(
select CriteriaID from ValidItemCriteria
where Destination = @DESTINATION
and LengthOfStay = @LENGTHOFSTAY
and Ages = @AGE
and ComponentType = @COMPONENTTYPE_1
and ValidItemType = @VALIDITEMTYPE_1
and ItemValue = @ITEMVALUE_1
)
Multiple @COMPONENTTYPE, @VALIDITEMTYPE,@ITEMVALUE can be sent in.
Instead of making multiple procs or copying the proc multiple times with an if statement at the top checking the number of parameters that aren't =''. Is there a way to exectue:
and CriteriaId in
(
select CriteriaID from ValidItemCriteria
where Destination = @DESTINATION
and LengthOfStay = @LENGTHOFSTAY
and Ages = @AGE
and ComponentType = @COMPONENTTYPE_1
and ValidItemType = @VALIDITEMTYPE_1
and ItemValue = @ITEMVALUE_1
)
and CriteriaId in
(
select CriteriaID from ValidItemCriteria
where Destination = @DESTINATION
and LengthOfStay = @LENGTHOFSTAY
and Ages = @AGE
and ComponentType = @COMPONENTTYPE_2
and ValidItemType = @VALIDITEMTYPE_2
and ItemValue = @ITEMVALUE_2
)
and CriteriaId in
(
select CriteriaID from ValidItemCriteria
where Destination = @DESTINATION
and LengthOfStay = @LENGTHOFSTAY
and Ages = @AGE
and ComponentType = @COMPONENTTYPE_3
and ValidItemType = @VALIDITEMTYPE_3
and ItemValue = @ITEMVALUE_3
)
Ignoring the 2nd 2 selects if @COMPONENTTYPE_2, @VALIDITEMTYPE_2,@ITEMVALUE_2 and @COMPONENTTYPE_3, @VALIDITEMTYPE_3,@ITEMVALUE_3 are = ''
Thanks for your help in advance.
When you create a Stored procedure, is it automatically stored under the default Filegoup?
How can I see under which Filegroup my Stored Procedures and Triggers are stored?
I didn't want to maintain similar/identical tables in a legacy FoxPro system and another system with SQL Server back end. Both systems are active, but some tables are shared.
Initially I was going to use a Linked Server to the FoxPro to pull the FP data when needed. This works. But, I've come up with what I believe is a better solution. Keep in mind that these tables are largely static - occassional changes, edits.
I will do a 1 time DTS from FP into SQL Server tables.
I then create INSERT and UPDATE triggers within FoxPro.
These triggers fire a stored procedure in FoxPro that establishes a connection to the SQL Server and fire the appropriate stored procedure on SQL Server to CREATE and/or UPDATE the corresponding table there.
In the end - the tables are local to both apps.
If the UPDATES or TRIGGERS fail I write to an error log - and in that rare case - I can manually fix. I could set it up to email me from within FoxPro as well if needed.
Here's the FoxPro and SQL Server code for reference for the Record Insert:
FOXPRO employee.dbf InsertTrigger:
employee_insert_trigger(VAL(Employee.ep_pk),Employ ee.fname,Employee.lname,Employee.email,Employee.us er_login,Employee.phone)
FOXPRO corresponding Stored Procedure:
FUNCTION EMPLOYEE_INSERT_TRIGGER
PARAMETERS wepk,wefname,welname,weemail,WEUSERID,WEPHONE
nhandle=SQLCONNECT('SS_PDITHP3','userid','password ')
IF nhandle<0
m.errclose=.f.
IF !USED("errorlog")
USE tisdata!errorlog IN SELECT(1)
m.errclose=.t.
ENDIF
SELECT errorlog
INSERT INTO errorlog (date, time, program,source,user) ;
values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nhandle<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username"))
IF m.errclose
USE IN errorlog
ENDIF
RETURN
ENDIF
nquery="exec ewo_sp_insertNewEmployee @WEPK ="+STR(wepk)+",@WEFNAME ='"+wefname+"',@WELNAME ='"+welname+"',@WEEMAIL ='"+weemail+"',@WEUSERID ='"+weuserid+"',@WEPHONE='"+wephone+"',@RETCODE =0"
nsucc=SQLEXEC(nhandle,nquery)
SQLDISCONNECT(nhandle)
IF nSucc<0
m.errclose=.f.
IF !USED("errorlog")
USE tisdata!errorlog IN SELECT(1)
m.errclose=.t.
ENDIF
SELECT errorlog
INSERT INTO errorlog (date, time, program,source,user) ;
values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nSucc<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username"))
IF m.errclose
USE IN errorlog
ENDIF
ENDIF
RETURN
SQL SERVER Stored Procedure called from FOXPRO Stored Procedure
CREATE procedure ewo_sp_insertNewEmployee (
@WEPK int,
@WEFNAME char(20),
@WELNAME char(20),
@WEEMAIL char(50),
@WEUSERID char(15),
@WEPHONE char(25),
@RETCODE int OUTPUT
)
AS
insert into WO_EMP (
WE_PK,
WE_FNAME,
WE_LNAME,
WE_EMAIL,
WE_USERID,
WE_PHONE
)
VALUES (
@WEPK,
@WEFNAME,
@WELNAME,
@WEEMAIL,
@WEUSERID,
@WEPHONE
)
IF @@ERROR <> 0
BEGIN
SET @RETCODE=@@ERROR
END
ELSE
BEGIN
-- SUCCESS!!
SET @RETCODE=0
END
return @RETCODE
GO