Calling Stored Procedures Inside Cursors
Aug 13, 2001
I have created a cursor using the following type of syntax:
DECLARE MyCursor CURSOR FOR
SELECT ID FROM tblEmployees
OPEN MyCursor
BEGIN
FETCH NEXT FROM MyCursor
END
CLOSE MyCursor
Instead of the SELECT statement (SELECT ID FROM tblEmployees), I actually have a very complex select statement. I have created a stored procedure to handle this select. However, I cannot find a way to call a stored procedure in place of the SELECT statement in the cursor. Is this possible? Thanks.
View 2 Replies
ADVERTISEMENT
Nov 1, 2007
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
View 1 Replies
View Related
Feb 18, 2006
I've been trying to copy tables from a linked server to a SQL ServerExpress database. Express seems to have no direct/automatic way to doit, so I've been looking into doing this by hand (i.e., with a T-SQLprocedure).I've discovered some system stored procedures that seem relevant (likesp_tables_ex and sp_columns_ex). But they're procedures, nottable-valued functions, so I'm not sure if I can actually do anythingwith the data they return.If I could get to the data, I thought maybe I could use a cusor and awhile loop to recreate the tables in Express.Is this crazy?Really could use some advice on this.Thanks,-Dan
View 1 Replies
View Related
Apr 18, 2007
Hi everybody,I am trying to alter a view inside a Stored Procedure and sql server is not allowing me to do so.Can we not have an alter view inside a Stored Procedure?Here is the code: CREATE PROCEDURE dbo.[UPDATE_VIEW_LINEITEMALLOTMENT] -- Add the parameters for the stored procedure here@MAINID int,@BE VARCHAR(8)ASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. --SET NOCOUNT ON; ALTER VIEW [Budget_Management_System].[dbo].[LineItem_OCAFund] AS SELECT TOP (100) PERCENT SUM(dbo.ALL_OCAFUND.AMOUNT) AS Expr2, dbo.APP_LINEITEM.LINENUM, dbo.APP_LINEITEM.PC, dbo.APP_LINEITEM.CATEGORY, dbo.APP_LINEITEM.ROWID, dbo.APP_LINEITEM.MAINID FROM dbo.APP_LINEITEM INNER JOIN dbo.ALL_ORG ON dbo.APP_LINEITEM.ROWID = dbo.ALL_ORG.LINEITEMID INNER JOIN dbo.ALL_OCAFUND ON dbo.ALL_ORG.ROWID = dbo.ALL_OCAFUND.ORGID INNER JOIN dbo.APP_AMENDMENT ON dbo.ALL_ORG.AMENDMENTID = dbo.APP_AMENDMENT.ROWID AND dbo.ALL_OCAFUND.AMENDMENTID = dbo.APP_AMENDMENT.ROWID GROUP BY dbo.APP_LINEITEM.LINEORDER, dbo.APP_LINEITEM.BE, dbo.APP_LINEITEM.MAINID, dbo.APP_LINEITEM.LINENUM, dbo.APP_LINEITEM.PC, dbo.APP_LINEITEM.CATEGORY, dbo.APP_LINEITEM.ROWID, dbo.APP_AMENDMENT.AMENDMENTORDER HAVING (dbo.APP_LINEITEM.BE = @BE) AND (dbo.APP_LINEITEM.MAINID = @MAINID) AND (dbo.APP_AMENDMENT.AMENDMENTORDER = 1) ORDER BY dbo.APP_LINEITEM.LINEORDER ENDthanks a lot,Murthy here
View 7 Replies
View Related
Jul 1, 2007
Hi All... I'm calling a stored procedure from C#. I'm sending it input parameters as follows:SqlCommand c = new SqlCommand("AddAuthor", myConnection);c.CommandType = CommandType.StoredProcedure;
c.Parameters.Add(new SqlParameter("@LastName", SqlDbType.VarChar, 100));c.Parameters["@LastName"].Value = "Last";
c.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.VarChar, 100));c.Parameters["@FirstName"].Value = "First";
In my opinion, that works nice - and it's easy. The reference I'm using says I can also specify "output" parameters - instead of supplying a value, one needs to change a property called direction.
But my stored procedure doesnt return any output parameters per se, but it does return a value. That is the last statement in the stored procedure is "RETURN @@IDENTITY" - it returns the identity field after an INSERT... So how do I get that value back in my C# code?
Thanks for the help in advance. Happy 4th to all! : ) -- Curt
View 4 Replies
View Related
Jan 16, 2008
Hi,
I was wondering if someone can tell me why my sub for calling stored procedures doesn't work.The string strFinFileId is not null.
I keep getting the following error System.IndexOutOfRangeException: Total
Here's my sub.
'//get GrandTotal Protected Sub GetGrandTotal(ByVal finfileid As String) Dim myConnection As SqlConnection Dim connString As String Dim strFinFileId As String Dim strGrandTotal As Decimal
strFinFileId = finfileid connString = ConfigurationManager.ConnectionStrings("PMOConnectionString1").ConnectionString
myConnection = New SqlConnection(connString) 'you need to provide password for sql server myConnection.Open()
Dim sql1 As String
sql1 = "GetGrandTotal " & Convert.ToInt32(strFinFileId)
Dim myCommand As SqlCommand
'Response.Write(sql1) 'Response.End()
myCommand = New SqlCommand(sql1, myConnection)
Dim reader As SqlDataReader = myCommand.ExecuteReader() While reader.Read() strGrandTotal = reader.Item("Total") LabelGrandTotal.Text = strGrandTotal 'Response.Write(strFinFileId) 'Response.End()
End While reader.Close() myConnection.Close() End Sub
Cheers
Mark :)
View 2 Replies
View Related
Apr 16, 2008
I have a stored procedure written to update a table:
The stored procedure has the following parameters:
@Original_TypeID int,
@Type_Desc varchar(35),
@Contact_Name varchar(20),
@Contact_Ad1 varchar(25),
@Contact_Ad2 varchar(25),
@Contact_City varchar(10),
@Contact_Phone varchar(12),
@Contact_Fax varchar(12),
@Contact_Email varchar(35)
And I want to call this procedure when the "update" button is clicked, my code is:Protected Sub UpdateButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("myConnectionString").ConnectionString)Dim myCommand As SqlCommandDim UpdteParam As New SqlParameter()
myConnection.Open()myCommand = New SqlCommand("[dbo].[sp_Update_Types]", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
????????????????????????myCommand.Parameters.Add(UpdateParam)
???????????myCommand.ExecuteNonQuery()
myConnection.Close()
End Sub
Can you please help me with the ????? areas? I don't know how to pass the parameters into the procedure, and also the second set of ??? s, I'm not sure if I am executing the command correctly.
Thank you.
View 3 Replies
View Related
Jun 21, 2001
I'm relatively new to SQL Server and I was wondering if there is any way to call a DLL from a regular stored procedure? I believe I've read that the extended stored procedure is a DLL and therefore should be able to call another DLL. Can this also be verified? We're looking to speed up some sections of Visual Basic code which does some heavy I/O work. I've also asked this once before but didn't get an absolute answer - can you write the extended stored procedures in Visual Basic or is C/C++ the only language supported right now?
Thanks.
Caroline Kaplonski
ckaplonski@buckconsultants.com
View 2 Replies
View Related
Jul 20, 2005
Hi AllI was wondering if there is a way to call a stored procedure from insideanother stored procedure. So for example my first procedure will call asecond stored procedure which when executed will return one record and iwant to use this data in the calling stored procedure. Is this possible ?Thanks in advance
View 18 Replies
View Related
Jul 20, 2005
Is it possible to have a stored procedure in database A while callingit from database B and have it manipulate the tables in database B(whatever the calling database happens to be)?We have a large-scale app that uses many complex stored procedures,and as of now, we're copying the SPs to every new database that iscreated, and it will soon become a nightmare for propagating updatesand fixes. We'd like to keep a master set of the SPs in one DB and"use" them from other DBs so that they only query data and manipulatetables in the calling DB. I hope someone has some suggestions. Thanks.
View 1 Replies
View Related
Jun 16, 2007
Hi,
Do you know how to write stored procedures inside another stored procedure in MS SQL.
Create procedure spMyProc inputData varchar(50)
AS
----- some logical
procedure spMyProc inputInsideData varchar(10)
AS
--- some logical
--- go
-------
View 5 Replies
View Related
Aug 25, 2007
How can we call Stored Procedure inside any SQL Statement
For Example.
If we have procedure name sprocCurrentPriority
select * from tablename where colmunname = exec sprocCurrentPriority
View 7 Replies
View Related
Oct 4, 2004
I'm using reporting services to build a report and I plan on using a stored procedure to gather my data for the report. My question is:
I know it's possible to call a stored procedure from a stored procedure, both within the same dB and in different dB's, but is it possible to call a SP that's on a different server? My gut says "definitely not. at least not easily." But I wanted to see if anyone had any thoughts on this before I pursue a different course of action.
View 3 Replies
View Related
Nov 3, 2000
can anyone tell me how to call SQL stored procedures in MS Access97...
appreciate any help
View 4 Replies
View Related
Jan 15, 2001
I am currently developing web pages based on data pulled from SQL Server with ASP. I've mastered the art of SELECTing, INSERTing, etc, but want to know if it is possible to run STored Procedures from a page by clicking on a button on a particualr web page.
View 1 Replies
View Related
Apr 3, 2008
I have a simple stored procedure:
create procedure sp_testres
@mult1 int,
@mult2 int,
@result int output
as
select @result = (10*@mult1) + @mult2
go
When I call it
declare @result int
exec sp_testres 5, 6, @result output
print @result
(Result is correctly shown as 56).
I then in C# wrote the following:
m_cmd.CommandText = "sp_testres";
SqlParameter param2 = new SqlParameter("@mult2", SqlDbType.Int);
param2.Value = 6;
SqlParameter param1 = new SqlParameter("@mult1", SqlDbType.Int);
param1.Value = 5;
SqlParameter param3 = new SqlParameter("@result", SqlDbType.Int);
param3.Direction = ParameterDirection.Output;
m_cmd.CommandType = CommandType.StoredProcedure;
m_cmd.Parameters.Add(param1);
m_cmd.Parameters.Add(param2);
m_cmd.Parameters.Add(param3);
m_cmd.ExecuteNonQuery();
This works and param3.Value holds the result value.
I also notice that I can supply the parameters in any order, and things work fine.
What I want to know is: can I call the stored procedure with parameters, where I haven't supplied the parameter name, and just rely on the parameter order matching instead?
View 5 Replies
View Related
Sep 7, 2006
Hello All,
We are trying to figure out how to make a stored procedure call and pass some inputs using C# and plain ADO. We are able to call an empty stored procedure but cannot pass parameters into a stored procedure.
Does anyone have sample code in C# whereby we can open a connection and pass inputs into a stored procedure? The following is a sample code we are using:
ADODB.Parameter param = new ADODB.Parameter();
param=cmd.CreateParameter("@StoreID", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput,sizeof(int) , 9);
cmd.Parameters.Append(param);
ADODB.Recordset rsInv = cmd.Execute(out objAffected, ref objAffected,-1 );
In the above example, we create a parameter of type integer and try to add it to the ADO Command object. It is supposed to return a recordset. It returns a recordset with record count -1.
Sincerely,
Dwight Kulkarni
View 3 Replies
View Related
Mar 23, 2007
I am working on a technical design of data integration ETL package which will be moving data from SQL Server Source to DB2 destination. I currently have two options, when moving data to DB2(IBM AS400). I can call a AS400 Stored Procedure, and pass in the data to the stored procedure, and perform the insert processing within the AS400 environment or I could do inserts from SSIS in a DFT and write individually to AS400 tables. My question is from a performance and good practice perspective, which method should I move forward with. I need a possible list of pros- and cons when using AS400 Sproc vs using SQL within SSIS? I would really appreciate response from individuals who have done something similar in the past. Thanks a lot and I am really looking forward to responses.
View 1 Replies
View Related
May 8, 2008
I am writing a set of store procedures (around 30), most of them require the same basic logic to get an ID, I was thinking to add this logic into an stored procedure.
The question is: Would calling an stored procedure from within an stored procedure affect performance? I mean, would it need to create a separate db connection? am I better off copying and pasting the logic into all the store procedures (in terms of performance)?
Thanks in advance
John
View 5 Replies
View Related
Feb 26, 2004
I am trying to call a stored procedure inside a SQL SELECT statement. Has anybody had to do this in the past? I have a SELECT statement in a Microsoft Access database and I need that SELECT statement to call the stored procedure in the SQL server. Any help would be appreciated
View 4 Replies
View Related
Oct 30, 2005
Hi.here is my code with my problem described in the syntax.I am using asp.net 1.1 and VB.NETThanks in advance for your help.I am still a beginner and I know that your time is precious. I would really appreciate it if you could "fill" my example function with the right code that returns the new ID of the newly inserted row.
Public Function howToReturnID(ByVal aCompany As String, ByVal aName As String) As Integer
'that is the variable for the new id.Dim intNewID As Integer
Dim strSQL As String = "INSERT INTO tblAnfragen(aCompany, aName)" & _ "VALUES (@aCompany, @aName); SELECT @NewID = @@identity"
Dim dbConnection As SqlConnection = New SqlConnection(connectionString)Dim dbCommand As SqlCommand = New SqlCommand()dbCommand.CommandText = strSQL
'Here is my problem.'What do I have to do in order to add the parameter @NewID and'how do I read and return the value of @NewID within that function howToReturnID'any help is greatly appreciated!'I cannot use SPs in this application - have to do it this way! :-(
dbCommand.Parameters.Add("@aFirma", aCompany.Trim)dbCommand.Parameters.Add("@aAnsprAnrede", aName.Trim)
dbCommand.Connection = dbConnection
TrydbConnection.Open()dbCommand.ExecuteNonQuery()
'here i want to return the new ID!Return intNewID
Catch ex As Exception
Throw New System.Exception("Error: " & ex.Message.ToString())
Finally
dbCommand.Dispose()dbConnection.Close()dbConnection.Dispose()
End Try
End Function
View 7 Replies
View Related
Nov 21, 2007
I writte a stored procedure for username , password ....
How can i call that stored procedure to verify the username & password
then if both match in the database redirected to next page..? in asp with c#.net back end programming
View 2 Replies
View Related
Dec 10, 2007
Hi guys, hoping one of you may be able to help me out. I am using VS 2005, and VB.net for a Windows application.
I have a table in SQL that has a list of Storedprocedures: Sprocs Table: SPID - PK (int), ID (int), NAME (string), TYPE (string)The ID is a Foreign key (corresponding to a Company ID), the name is the stored procedure name, and Type (is the type of SP).
On my application I need to a certain SP depending on the company selected and what page you are on. I have a seperate SP that passes in parameters for both Company, and Type and should output the Name value:
ALTER PROCEDURE [dbo].[S_SPROC] ( @ID int, @TYPE CHAR(10), @NAME CHAR(20) OUTPUT )AS
SELECT @NAME = NAME FROM SPROCSWHERE [ID] = @IDAND [TYPE] = @TYPE
Unfortunately I dont seem to be able to get the output in .Net, or then be able to fill my dataset with the Stored Procedure.Has anyone done something similar before, or could point me in the right direction to solving this problem.
ThanksPhil
View 8 Replies
View Related
Apr 29, 2004
Hi, all
I'm using Sql server 2000
I want to make select statement dynamically and return table using function.
in sp, I've done this but, in function I don't know how to do so.
(I have to create as function since our existing API..)
Following is my tials...
1.
alter Function fnTest
( @fromTime datetime, @toTime datetime)
RETURNS Table
AS
RETURN Exec spTest @from, @to
GO
Yes, it give syntax error..
2. So, I found the following
From Sql Server Books Online, Remark section of CREATE FUNCTION page of Transact-SQL Reference , it says following..
"The following statements are allowed in the body of a multi-statement function. Statements not in this list are not allowed in the body of a function: "
.....
* EXECUTE statements calling an extended stored procedures.
So, I tried.
alter Function fnTest
( @fromTime datetime, @toTime datetime)
RETURNS Table
AS
RETURN Exec master..xp_msver
GO
It doesn't work... syntax err...
Here I have quick question.. How to execute statements calling an extended stored procedures. any examples?
Now, I'm stuck.. how can I create dynamic select statement using function?
I want to know if it's possible or not..
View 13 Replies
View Related
Jul 13, 2004
hai guys
how should we have to cal the store procedure inside the same store procedure.
for Example
Create procedure A
as
Begin
Select * from mytable
execute A
end.
is this the correct one
View 3 Replies
View Related
Feb 13, 2008
Hi all,
I try to learn "How to Access Stored Procedures with ADO.NET 2.0 - VB 2005 Express: (1) Handling the Input and Output Parameters and (2) Reporting their Values in VB Forms". I found a good article "Calling Stored Procedures from ADO.NET" by John Paul Cook in http://www.dbzine.com/sql/sql-artices/cook6. I downloaded the source code into my VB 2005 Express:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form_Cook
Inherits System.Windows.Form.Form
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents GroupBox1 As System.Windows.Forms.GroupBox
Friend WithEvents labelPAF As System.Windows.Forms.Label
Friend WithEvents labelNbrPrices As System.Windows.Forms.Label
Friend WithEvents UpdatePrices As System.Windows.Forms.Button
Friend WithEvents textBoxPAF As System.Windows.Forms.TextBox
Friend WithEvents TenMostExpensive As System.Windows.Forms.Button
Friend WithEvents grdNorthwind As System.Windows.Forms.DataGrid
Friend WithEvents groupBox2 As System.Windows.Forms.GroupBox
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.GroupBox1 = New System.Windows.Forms.GroupBox()
Me.labelPAF = New System.Windows.Forms.Label()
Me.labelNbrPrices = New System.Windows.Forms.Label()
Me.textBoxPAF = New System.Windows.Forms.TextBox()
Me.UpdatePrices = New System.Windows.Forms.Button()
Me.groupBox2 = New System.Windows.Forms.GroupBox()
Me.TenMostExpensive = New System.Windows.Forms.Button()
Me.grdNorthwind = New System.Windows.Forms.DataGrid()
Me.GroupBox1.SuspendLayout()
Me.groupBox2.SuspendLayout()
CType(Me.grdNorthwind, System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'
'GroupBox1
'
Me.GroupBox1.Controls.AddRange(New System.Windows.Forms.Control() {Me.labelPAF, Me.labelNbrPrices, Me.textBoxPAF, Me.UpdatePrices})
Me.GroupBox1.Location = New System.Drawing.Point(8, 8)
Me.GroupBox1.Name = "GroupBox1"
Me.GroupBox1.Size = New System.Drawing.Size(240, 112)
Me.GroupBox1.TabIndex = 9
Me.GroupBox1.TabStop = False
'
'labelPAF
'
Me.labelPAF.Location = New System.Drawing.Point(8, 16)
Me.labelPAF.Name = "labelPAF"
Me.labelPAF.Size = New System.Drawing.Size(112, 32)
Me.labelPAF.TabIndex = 2
Me.labelPAF.Text = "Enter Price Adjustment Factor"
'
'labelNbrPrices
'
Me.labelNbrPrices.Location = New System.Drawing.Point(8, 80)
Me.labelNbrPrices.Name = "labelNbrPrices"
Me.labelNbrPrices.Size = New System.Drawing.Size(216, 16)
Me.labelNbrPrices.TabIndex = 5
'
'textBoxPAF
'
Me.textBoxPAF.Location = New System.Drawing.Point(120, 16)
Me.textBoxPAF.Name = "textBoxPAF"
Me.textBoxPAF.TabIndex = 0
Me.textBoxPAF.Text = ""
'
'UpdatePrices
'
Me.UpdatePrices.Location = New System.Drawing.Point(8, 48)
Me.UpdatePrices.Name = "UpdatePrices"
Me.UpdatePrices.Size = New System.Drawing.Size(88, 23)
Me.UpdatePrices.TabIndex = 6
Me.UpdatePrices.Text = "Update Prices"
'
'groupBox2
'
Me.groupBox2.Controls.AddRange(New System.Windows.Forms.Control() {Me.TenMostExpensive, Me.grdNorthwind})
<Part 1----To be continued due to the length of this post>
View 1 Replies
View Related
Oct 4, 2007
Can someone please take a quick look at this and tell me what I'm doing wrong I'm sure it's something simple. I'm a little new to stored procedures but I've been using SQL and T-SQL for quite some time, I've just always used inline queries with my ASP. This procedure needs to be run monthly by me or another person I grant access to and will update sales information that our sales staff will be paid commission on. I need to supply the start date and and end date for the query and it will pull this information from our business system which is hosted remotely by a third party and pull it into our local SQL server where we can run commission reports against it. (I hope this is enough information you can understand where I'm trying to go with this). I know my problem right now lies in how I'm trying to call the variable inside of my T-SQL. Any help is appreciated. This is an old Unix system and it stores the date as YYYYMMDD as numeric values incase someone wonders why I have dimed my dates as numeric instead of as datetime =)
I'm using a relativity client to create an ODBC connection to the UNIX server and then using a linked server to map a connection in SQL this is the reason for the OpenQuery(<CompanyName>
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: XXXXXXXXXXXXX
-- Create date: 10/4/2007
-- Description: This proc is designed to pull all CSA
-- part sales from XXXXXX business system and upload them
-- into the local XXXXXXXX Database for commission reporting
-- =============================================CREATE proc usp_CSAPartsSalesUpdate@date1 int, @date2 int
As
INSERT INTO CSAPartsSales ( CSA, CustomerNumber, CustomerName, Location, InvoiceNumber, InvoiceDate, InvoiceAmount )
SELECT SalesRoute, HInvCust, CustOrCompName, HInvLoc, HInvNo, HInvDate, HInvAmt From OpenQuery(<CompanyName>, 'Select CPBASC_All.SalesRoute, PMINVHST.HInvCust, CPBASC_All.CustOrCompName, PMINVHST.HInvLoc, PMINVHST.HInvNo, PMINVHST.HInvDate, PMINVHST.HInvAmtFROM PMINVHST INNER JOIN CPBASC_All ON PMINVHST.HInvCust = CPBASC_All.CustomerNo
WHERE (((PMINVHST.HInvAmt)<>0) AND ((PMINVHST.HInvDate)>=''' + @date1 + ''' And (PMINVHST.HInvDate)<=''' + @date2 + ''') AND ((Trim([CPBASC_All].[SalesRoute]))<>'''' And (Trim([CPBASC_All].[SalesRoute]))<>''000''))')
In this example date1 will be equal to 20070901 and date2 will be equal to 20070930 so I can pull all CSA sales for the month of September.
This is the error message I get when I try to create the proc:
Msg 102, Level 15, State 1, Procedure usp_CSAPartsSalesUpdate, Line 17
Incorrect syntax near '+'.
~~~ Thanks All~~~
View 9 Replies
View Related
Jul 20, 2005
Hi!I have a scalar function that returns integer:xview (int)Now, I'm trying to build a procedure that has the following selectinside:select atr1, xview(atr2)from tablenameBut, I get the 'Invalid name' error when I try to execute thatprocedure.If I got it right, I must use user.fn_name() syntax, but I cannot usedbo.xview() inside my procedure since it means xview will always beexecuted as dbo, which is unaccaptable.I'm a bit confused, so any hint is very welcomed.Thanks!Mario.
View 4 Replies
View Related
Sep 3, 2007
Hello
I use SQL Server 2005 Reporting Service to develop all my Reports. i design the report and deploy to the report server. i have created a Web application where i use the above report to be shown inside a ReportViewer page.
when any user view's the report i also want to give them the subscription screen inside the report viewer. BIG QUESTION is how do i call the subscription screen of a specific report inside the report viewer?
any help is appreciated
/Chandresh Soni
View 5 Replies
View Related
May 15, 2008
I have several UDFs created. Inside one of the UDFs I need to execute a dynamic SQL statement and then take that result and do something else with it, before returning the final value.
I know you can not execute a stored proce from inside a function. I also know you can not use the EXEC statement.
I did read that you could use an external stored procedure and/or managed CLR procedures inside a function.
I have created a managed procedure CLR (C#) that simply executes a passed statemetn and returns the value to the calling routine. I have this all coded and is working fine.
However, I am struggling with knowing how to call this CLR procedure from inside my function, seeing how I can not use EXEC statement.
Any advice on how to do this?
Thanks,
Bruce
View 1 Replies
View Related
May 26, 2008
Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it?
What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results.
Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application.
However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password.
Looking forward for replies from expert here. Thanks in advance.
Note: Hope my explaination here clearly describe my current problems.
View 4 Replies
View Related
Nov 15, 1999
Hi all!
Is there a possibility to call (and receive records) SQL Server (or other databases) procedure from MS Access?
View 1 Replies
View Related
Jul 23, 2005
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
View 11 Replies
View Related
