Hi
Can someone please shed some light as to how this can be done.
With the below sql statement as you can see I don't want to declare the table name but would rather the table name be passed in via a parameter. I've tried declaring it as a varchar parameter but it doesnt seem to like it if I don't add a valid table name. Any ideas how this can be done. Thanks.
select * from @tableName where condition = condition
Does anyone know the syntax to reference a table in a stored procedure using a parameter? I'm trying to make a stored procedure that will "SELECT INTO" a table as the passed parameter. I keep getting an syntax error when I use this:
CREATE PROCEDURE make_table
@tablename varchar(20)
AS
SELECT * FROM old_table
INTO @tablename
I've tried it several ways, but I can't get it to work, and I haven't found any examples of this anywhere. Thanks in advance for any tips.
Hi all.
I want something like this :
create proc myProc ( @num int ,@name varchar(80))
as
select @num = max(field1) from @name
but there is a problem that the @name is a varchar and the error is :
Server: Msg 170, Level 15, State 1, Procedure sp_myProc, Line 3
Line 3: Incorrect syntax near '@name'.
I tried
select @num = max(field1) from object_id(@name)
and the error was the same
is ther another way than :
exec ("declare @num int select @num = max(field1) insert into anotherTable values( @num) " ) ?
thx
Eyal Peleg
Hello,
I would like to pass into a stored procedure the name of a table. Following code does not work.
USE pubs
GO
CREATE PROC test1
@tableName varchar(40)
AS
SELECT * FROM @tableName
EXEC test1 @tableName='authors'
I would appreciate it very much if someone could help me with this.
Thanks
helloI am trying to do a procedure that :1 - Take a table name, a field name and a value as parameter2 - check if the fieldname=value in the tablename table3 - returns 1 if so, 0 if notA -I tried :drop procedure checkmytableGOcreate procedure checkmytable@p_table nvarchar(50),@p_field nvarchar(50),@p_value nvarchar(50)asif exists ( select * from @p_table where @p_field = @p_value)return 1elsereturn 0declare@res intexec @res=checkmytable 'mytable' , 'myfield' , '456123'print @resin this case, I always get the error : 'must declare @p_table'B -I trieddrop procedure checkmytableGOcreate procedure checkmytable@p_table nvarchar(50),@p_field nvarchar(50),@p_value nvarchar(50)asreturn exec( 'if exists (select * from' + @p_table + ' where ' + @p_field +' = ' + @p_value + ') return 1 else return 0' )GOdeclare@res intexec @res=checkmytable 'AM_ASSET' , 'ASSET_ID' , '18705'print @resin this case, I always get 0any help ?
View 1 Replies View RelatedThis code works EXACTLY as it should on a development server that has a Framework 1.1 hotfix (operating in the 'localhost' webserver environment) but not on a production server (in a live www. web app) that does NOT have the 1.1 hotfix. Is there a code problem (if so, why should it work in the localhost) or is the hotfix on the development server needed on the production server.
(in a button sub that does an update)...Dim currentUser As New AccBusiness.User( _ CType(Context.User, SitePrincipal)) Dim DDName As String = currentUser.DDName Dim NosData As String Dim AccountDetails As New SecureNums(txtDDPhone.Text, txtDDAddress.Text) NosData = AccountDetails.EncryptedData() AccountDetails.UpdateAcctDetails()----------------------(business object for the update)... Public Function UpdateAcctDetails() As Boolean Dim DataUserAcctNo As New Data.SecureCard(myModuleSettings.ConnectionString) Return DataUserAcctNo.UpdateAcctDetails( _ myDDName, _ myNosData) End Function--------------------------------(data object)...Public Function UpdateAcctDetails(ByVal DDName As String, _ ByVal NosData As String) As Boolean Dim rowsAffected As Integer Dim parameters As SqlParameter() = { _ New SqlParameter("@DDName", SqlDbType.VarChar, 50), _ New SqlParameter("@NosData", SqlDbType.VarChar, 512)} parameters(0).Value = DDName parameters(1).Value = NosData RunProcedure("sp_Accounts_UpdateAccount", parameters, rowsAffected) Return CBool(rowsAffected = 1) End Function------------------------------and the sproc:ALTER PROCEDURE sp_Accounts_UpdateAccount@DDNameVarChar(50),@NosDataVarChar(512)AsUPDATE Accounts_UsersSet NosData = @NosDataWHERE DDName = @DDName------------------------------------------------------------
It all works on the development server but on the production server it gives me an error :SProc sp_Accounts_UpdateAccount expects parameter "@DDName" which was not supplied.
Can anyone suggest why this works in one environment and not the other?
Thx for looking/Reid C.
I'm trying to build a sproc that will return rows even if some of the parameters are blank. For example; if a user does not enter a priority, a status, or a caller the sproce should still return rows based on the other parameters.
Can anyone help me find a way to modify my sproc bellow to allow this? I think the way I have it bellow will only return those rows where the user has entered a parameter or the record has a null in the field.ALTER PROCEDURE dbo.ContactManagementAction
(
@ClientID int,
@Priority int,
@TStart datetime,
@TEnd datetime,
@Status nvarchar,
@ConTypeID int,
@Caller nvarchar,
@Keyword nvarchar
)
AS
SELECT Task_ID, ClientID, Priority, ActionDate, Subject, Note, Status, CompletionDate, TaskDocument, ReminderDate, Reminder, ReminderTime, Sol_ID,
DateEntered, EnteredBy, Caller, ContactTypeID, DueDate
FROM tblTasks
WHERE (ClientID = @ClientID) AND (Priority = @Priority) OR (Priority IS NULL) AND (ActionDate BETWEEN @TStart AND @TEnd) AND (Status = @Status) OR (Status IS NULL) AND
(ContactTypeID = @ConTypeID) OR (ContactTypeID IS NULL) AND (Caller = @Caller) OR (Caller IS NULL) AND (Subject LIKE @Keyword) OR (Subject IS NULL)
RETURN
I need to use a parameter in a stored procedure that is optional and if it is optional I need all records returned, even if there is an entry in the field that the parameter is appllied to. Is this possible? I know I can assign a default value. but the value in the field could be one of many choices. I want to be able to specify a choice and have only those records returned or leave it blank and return all records.
Thanks for any help,
Hi,
I've tried that but it does not work:
SELECT Field FROM Table WHERE Field LIKE @parameter
thanks
I am quite new to SSRS and am having some difficulties in trying to develop a new report via Business Intelligence Studio based on a stored procedure which requires the input of 1 parameter, and also has an optional parameter which I default to NULL in the sproc.
When I create my dataset I select the given sproc I want and when I attempt to execute it, I am prompted for the parameters the sproc expects.
However, when I enter a value in the dialog for the required Parameter I get a SQL error indicating that the parameter the sproc expects was not supplied. I have profiled the call and see the attempt to execute the sproc, but no parameter value.
Can some one tell me why the value I enter is not being passed to the sproc in my database ? Is there some special syntax that I need to use ?
I have scanned a number of sites & through the books I have and can't find anything on this. From what I have read, when I exec my sproc the parameters get recongnized & I can just enter my values. This doesn't seem to be the case.
Any help and/or suggestions are appreciated !!!
Thanks.
Hello,
I am having trouble with a SPROC and I was wondering if anyone has had similar problems. I have a web form where users enter in a large amount of text, the web app saves that data via a SPROC into a varchar(8000) field in a SQL table. However, any amount of text beyond 255 gets cut off. I have double checked and tested my code and I dont seem to find the problem. Below is the beginning of my SPROC and as you can see, I have the correct data type and length. I have also verified the data type and length on the table and it is set at varchar(8000). Any ideas?
SPROC CODE
-----------------------------------------------
CREATE PROCEDURE Add_ProductsReview
(
@Pros varchar(100) = null,
@Cons varchar(100) = null,
@Title varchar(100),
@Body varchar(8000) -- This is the parameter with problems
)
AS......
Thanks!
In the below sproc I want to create a view based on the incoming parameter, which is a date passed on from VB in the format 01-01-2006.
Yet getting that to work, seems quite a challenge.
I've been dabbling a bit with the sproc and QA, passing the date parameter to it in varous forms, using both single, double and triple quotationmarks and changing the parameter type to datetime.
I either wind up with a syntax error " Incorrect syntax near '01' " or " Unclosed quotation mark before the character string ".
Anyone have a guess at how to fix this issue?
Cheers, Trinsan
REATE PROCEDURE KONTROL_DRdobbeltrapportering
@rapdato as varchar
AS
if exists (select * from dbo.sysobjects where id = object_id(N'[KONTROL_vw_DRdobbeltrappbasis]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [KONTROL_vw_DRdobbeltrappbasis]
EXEC('
CREATE VIEW KONTROL_vw_DRdobbeltrappbasis
AS
SELECT Gramex_DW.dbo.Programhoved.[Rettet af DR],
Gramex_DW.dbo.Programlinie.Stationskode,
Gramex_DW.dbo.Programlinie.Udsendelsesdato,
Gramex_DW.dbo.Programlinie.[Udsendelses starttid],
Gramex_DW.dbo.Programlinie.Rapporteringsdato, Gramex_DW.dbo.Programlinie.Mediekode,
Gramex_DW.dbo.Programlinie.MUSANummer, Replace(Left(convert(varchar, [Gramex-ID]),11),''.'','''') AS GramexID,
Gramex_DW.dbo.Programlinie.Side, Gramex_DW.dbo.Programlinie.Tracknummer,
Gramex_DW.dbo.Programlinie.ISRC, Gramex_DW.dbo.Programlinie.Producentlandekode,
Gramex_DW.dbo.Programlinie.Spilletid, Gramex_DW.dbo.Programlinie.Minuttakst,
Gramex_DW.dbo.Programlinie.[Afregnet Station],
Gramex_DW.dbo.Programlinie.[Afregnet den],
Gramex_DW.dbo.Programlinie.[Afregnings ID], Gramex_DW.dbo.Programlinie.[Ur-opførelse],
Gramex_DW.dbo.Programlinie.Talestof, Gramex_DW.dbo.Programlinie.[Betalt af DR],
Gramex_DW.dbo.Programlinie.Kending, Gramex_DW.dbo.Programlinie.[Oprettelses ID],
Gramex_DW.dbo.Programlinie.[Oprettet den], Gramex_DW.dbo.Programlinie.[Oprettet af],
Gramex_DW.dbo.Programlinie.[Ændret den], Gramex_DW.dbo.Programlinie.[Ændret af],
Gramex_DW.dbo.Programlinie.[Eventuel delskæring], Gramex_DW.dbo.Programlinie.Plademærkenavn,
Gramex_DW.dbo.Programlinie.Katalogbetegnelse, Gramex_DW.dbo.Programlinie.Tracktitel,
Gramex_DW.dbo.Programlinie.Selskabsnummer, Gramex_DW.dbo.Programlinie.Indspilningsår,
Gramex_DW.dbo.Programlinie.Fonogramtitel, Gramex_DW.dbo.Programlinie.Plademærkenummer,
Gramex_DW.dbo.Programlinie.[Antal opførelser], Gramex_DW.dbo.Programlinie.[Rec nummer],
Gramex_DW.dbo.Programlinie.Indslagsnummer, Gramex_DW.dbo.Programlinie.[Afspillet gange],
Gramex_DW.dbo.Programlinie.[Afspillet minutter], Gramex_DW.dbo.Programlinie.Spilletidsløbenummer,
Gramex_DW.dbo.Programlinie.Pointtildelt, Gramex_DW.dbo.Programlinie.[Rettet af Gramex],
Gramex_DW.dbo.Programlinie.[Pgmkorlinie løbenummer], Gramex_DW.dbo.Programlinie.[Tape Kilde],
Gramex_DW.dbo.Programlinie.[Hoved artist], Gramex_DW.dbo.Programhoved.Produktionsnummer
FROM Gramex_DW.dbo.Programlinie
INNER JOIN Gramex_DW.dbo.Programhoved
ON (Gramex_DW.dbo.Programlinie.Stationskode = Gramex_DW.dbo.Programhoved.Stationskode)
AND (Gramex_DW.dbo.Programlinie.Udsendelsesdato = Gramex_DW.dbo.Programhoved.Udsendelsesdato)
AND (Gramex_DW.dbo.Programlinie.[Udsendelses starttid] = Gramex_DW.dbo.Programhoved.[Udesendelses starttid])
WHERE Gramex_DW.dbo.Programlinie.Rapporteringsdato >= ' + @rapdato + '
AND (Gramex_DW.dbo.Programlinie.Mediekode <> 1) AND
(Gramex_DW.dbo.Programlinie.Spilletid >= ''00:01:00'') AND (Gramex_DW.dbo.Programlinie.Stationskode <> ''TV2'') AND
(Gramex_DW.dbo.Programlinie.Stationskode <> ''TV2 ZULU'') AND (Gramex_DW.dbo.Programlinie.Stationskode <> ''TV2 CHARLIE''')
GO
We have a service written in c# that is processing packages of xml that contain up to 100 elements of goods consignment data.
In amongst that element is an identifier for each consignment. This is nvarchar(22) in our table. I have not observed any IDs that are different in length in the XML element.
The service picks up these packages from MSMQ, extracts the data using XPATH and passes the ID into the SPROC in question. This searches for the ID in one of our tables and returns a bool to the service indicating whether it was found or not. If found then we add a new row to another table. If not found then it ignores and continues processing.
The service seems to be dealing with a top end of around 10 messages a minute... so a max of about 1000 calls to the SPROC per minute. Multi-threading has been used to process these packages but as I am assured, sprocs are threadsafe.It is completing the calls without issue but intermittently it will return FALSE. For these IDs I am observing that they exist on the table mostly (there are the odd exceptions where they are legitimately missing).e.g Yesterday I was watching the logs and on seeing a message saying that an ID had not been found I checked the database and could see that the ID had been entered a day earlier according to an Entered Timestamp.
USE [xxxxxxxxxx]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[code]....
So on occasions (about 0.33% of the time) it is failing to get a bit 1 setting in @bFound after the SELECT TOP(1).
change @pIdentifier nvarchar(25) to nvarchar(22)
Trim any potential blanks from either side of both parts of the identifier comparison
Change the SELECT TOP(1) to an EXISTS
The only other thought is the two way parameter direction in the C# for the result OUTPUT. I have been unable to replicate this using a test app and our test databases. Have observed selects failing to find even though the data is there, like this before?
I have an optional param passed to my sproc @Pid if that is populated i want to have it part of where clause, if it is null then not in where
I know this is wrong, and very new to sqlsrver:
WHERE (Pos = 'T') AND (ofDate= @OfDate)
IF not @PID is null
AND (PID = @PID )
GROUP BY
bla bla bla
Thanks
Could someone please tell me why the following SELECT statement...
SELECT ID, SpecimenNr, ScientificName, Locality, TaxonFROM petrander.QueryViewWHERE (InstitutionCode = 1) AND (Collectioncode = 1) AND (ScientificName LIKE N'%le%') AND (Locality LIKE N'%Fakse%') AND (22 IN (ParentID1, ParentID2, ParentID3, ParentID4, ParentID5, ParentID6, ParentID7, ParentID8))
...gives me 9 rows back, but embedding the exact same statement in the following sproc...
set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [petrander].[DynamicQuery] @taxparent int = NULL, @museum int = NULL, @collection int = NULL, @binomen Nvarchar(254) = NULL, @locality Nvarchar(254) = NULLAS SELECT ID, SpecimenNr, ScientificName, Locality, Taxon FROM QueryView WHERE InstitutionCode = COALESCE(@museum, InstitutionCode) AND CollectionCode = COALESCE(@collection, CollectionCode) AND ScientificName LIKE 'N%' + @binomen + '%' AND Locality LIKE 'N%' + @locality + '%' AND (@taxparent IN (ParentID1, ParentID2, ParentID3, ParentID4, ParentID5, ParentID6, ParentID7, ParentID8))
...and passing the exact same parameter values to with the following execute statement...
USE [Geomusdb]
GO
DECLARE @return_value int
EXEC @return_value = [petrander].[DynamicQuery]
@museum = 1,
@collection = 1,
@binomen = N'le',
@locality = N'Fakse'
SELECT 'Return Value' = @return_value
GO
gives me 0 rows!? What is different!?
Any help is greatly appreciated...
I have attached the results of checking an Update sproc in the Sql database, within VSS, for a misbehaving SqlDataSource control in an asp.net web application, that keeps telling me that I have too many aurguments in my sproc compared to what's defined for parameters in my SQLdatasource control.....
No rows affected.
(0 row(s) returned)
No rows affected.
(0 row(s) returned)
Running [dbo].[sp_UPD_MESample_ACT_Formdata]
( @ME_Rev_Nbr = 570858
, @A1 = No
, @A2 = No
, @A5 = NA
, @A6 = NA
, @A7 = NA
, @SectionA_Comments = none
, @B1 = No
, @B2 = Yes
, @B3 = NA
, @B4 = NA
, @B5 = Yes
, @B6 = No
, @B7 = Yes
, @SectionB_Comments = none
, @EI_1 = N/A
, @EI_2 = N/A
, @UI_1 = N/A
, @UI_2 = N/A
, @HH_1 = N/A
, @HH_2 = N/A
, @SHEL_1 = 363-030
, @SHEL_2 = N/A
, @SUA_1 = N/A, @SUA_2 = N/A
, @Cert_Period = 10/1/06 - 12/31/06
, @CR_Rev_Completed = Y ).
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[sp_UPD_MESample_ACT_Formdata].
The program 'SQL Debugger: T-SQL' has exited with code 0 (0x0).
And yet every time I try to update the record in the formview online... I get
Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
I have gone through the page code with a fine tooth comb as well as the sproc itself. I have tried everything I can think of, including creating a new page and resetting the fields, in case something got broken that I can't see.
Does anyone have any tips or tricks or info that might help me?
Thanks,
SMA49
I have the below code and have two questions.
1) With the current delete control, the system breaks stating: System.Data.SqlClient.SqlException: Must declare the variable '@doc_area_id'.
2) Currently the Area Type column brings up all records set as '1'. It shows the value '1' for each column. I would like it to state the text 'Shared Area' under the Area Type Column where it is set as '1' in the database.
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlManageSharedArea" CellPadding="4" ForeColor="#333333" BorderStyle="Solid" BorderColor="Black" BorderWidth="1px">
<Columns>
<asp:BoundField DataField="doc_area_name" HeaderText="Area Name" SortExpression="doc_area_name" />
<asp:BoundField DataField="doc_area_type" HeaderText="Area Type" SortExpression="doc_area_type" />
<asp:CheckBoxField DataField="doc_area_default" HeaderText="Default" SortExpression="doc_area_default" />
<asp:HyperLinkField HeaderText="Edit" NavigateUrl="~/Admin/EditDocumentArea.aspx" Text="Edit" ><ItemStyle ForeColor="Black" /><ControlStyle ForeColor="Black" /></asp:HyperLinkField>
<asp:CommandField ShowDeleteButton="True" HeaderText="Delete" ><ItemStyle ForeColor="Black" /><ControlStyle ForeColor="Black" /></asp:CommandField>
</Columns>
<FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#E3EAEB" />
<EditRowStyle BackColor="#7C6F57" />
<SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
<PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
<asp:SqlDataSource ID="SqlManageSharedArea" runat="server" ConnectionString="<%$ ConnectionStrings:CPS_docshareConnectionString %>"
SelectCommand="SELECT [doc_area_id], [doc_area_name], [doc_area_type], [doc_area_default] FROM [document_area] WHERE [doc_area_type] = 1"
DeleteCommand="DELETE FROM document_area WHERE [doc_area_id] = @doc_area_id">
</asp:SqlDataSource>
Any help would be greatful!
Hi, i am trying to run the following query in SQL Server CE:
SELECT @V1=CAST(SCOPE_IDENTITY() AS bigint)
SELECT @V1
INSERT INTO [ResourceItem] ([PackageId],[ResourceXml]) VALUES (@V1,@V6)
It gives me an error "near SELECT".
I replaced SCOPE_IDENTITY() with @@IDENTITY but it didnt help.
I think it is something related to storing values such as @foo = something.
How can i declare variables in SQL CE?
Pls help
Thank You
I am learning T-SQL syntax and I am very familiar with it, however how would I do the following:
We have a table that actually has a column that contains SQL statements. I want to build a SQL statement in Reporting Services that is going to take that column to build a "dynamic" SQL statment and then I will use the exec sp_executesql statement.
Do I need to declare a parameter, or in SQL is there such thing as a variable?
So if I have:
DECLARE @sql nvarchar(4000)
SELECT AdHocSQL from TheTable
SET @sql=AdHocSQL
Would this work? Is this syntatically correct? Or should I be doing this some other way?
The report is sort of a summary report that has about 250 different items and each item has different data to get from different tables.
Thanks for the information.
I'm sorta new with using stored procedures and I'm at a loss of how to achieve my desired result.
What I am trying to do is retrieve a value from a table before it is updated and then use this original value to update another table. If I execute the first called sproc in query analyzer it does return the value I'm looking for, but I'm not really sure how to capture the returned value. Also, is there a more direct way to do this?
Thanks,
Peggy
Sproc that is called from ASP.NET:
ALTER PROCEDURE BP_UpdateLedgerEntry
(
@EntryLogID int,
@ProjectID int,
@NewCategoryID int,
@Expended decimal(10,2)
)
AS
DECLARE@OldCategoryID int
EXEC @OldCategoryID = BP_GetLedgerCategory @EntryLogID
UPDATE
BP_EntryLog
SET
ProjectID = @ProjectID,
CategoryID = @NewCategoryID,
Expended = @Expended
WHERE
EntryLogID = @EntryLogID
EXEC BP_UpdateCategories @ProjectID, @NewCategoryID, @Expended, @OldCategoryID
Called Sprocs:
*********************************************
BP_GetLedgerCategory
*********************************************
ALTER PROCEDURE BP_GetLedgerCategory
(
@EntryLogID int
)
AS
SELECT CategoryID
FROM BP_EntryLog
WHERE EntryLogID = @EntryLogID
RETURN
*********************************************
BP_UpdateCategories
*********************************************
ALTER PROCEDURE BP_UpdateCategories
(
@ProjectID int,
@NewCategoryID int,
@Expended decimal(10,2),
@OldCategoryID int
)
AS
UPDATE
BP_Categories
SET CatExpended = CatExpended + @Expended
WHERE
ProjectID = @ProjectID
AND
CategoryID = @NewCategoryID
UPDATE
BP_Categories
SET CatExpended = CatExpended - @Expended
WHERE
ProjectID = @ProjectID
AND
CategoryID = @OldCategoryID
create procedure dbo.GetZipID( @City varchar(30), @State char(2), @Zip5 char(6))
as
DECLARE @CityID integer
declare @StateID integer
declare @ZipID integer
set @ZipID=2
set @Zip5=lTrim(@Zip5)
if @Zip5<>''
SET @ZIPID = (select Min(lngZipCodeID) AS ZipID from ZipCodes where strZipCode=@Zip5)
if @ZipID is null
set @CityID= EXEC GetCityID(@City);
set @StateID= EXEC GetStateID(@State);
insert into ZipCodes(strZipCode,lngStateID,lngCityID) values(@Zip5,@StateID,@CityID)
if @@ERROR = 0
SET @ZIPID = @@Identity
select @ZIPID
GetCityID and GetStateID are two stored procs, how do I execute those two stored procs
in the above stored proc? I mean what is the syntax??
Tks
I have a job that runs in sql 2008r2 that declares a value to a variable. This variable is passed to a function. I need to include more values in and am unsure how to do this. @InCo could be 4,5,6,or 7.
See below.
Declare @ReportDateIN [datetime]=GETDATE(),
@InCo varchar(max) = 4,
@OutCo varchar(max) = 8,
@IncludeDetailIN [int]= 1,
@IncludeReleasedIN [int]= 1,
@IncludeHoldingIN [int]= 1
[Code] .....
I am still having problems with my cursors. I am receiving the following errors when I execute my Stored Procedure:
Msg 16915, Level 16, State 1, Procedure BatchNonMons2, Line 27
A cursor with the name 'MyCursor' already exists.
Msg 16905, Level 16, State 1, Procedure BatchNonMons2, Line 31
The cursor is already open.
Msg 16915, Level 16, State 1, Procedure BatchNonMons2, Line 37
A cursor with the name 'vwCursor' already exists.
Msg 16905, Level 16, State 1, Procedure BatchNonMons2, Line 38
The cursor is already open.
Here is the code causing the problem:
CREATE PROCEDURE [dbo].[BatchNonMons2] AS
BEGIN
/* Create a new hdr each time the Sys/Prin changes in the Dtl rec */
/* Define every field you want to output */
DECLARE @Batch_Type int
DECLARE @Batch_Num int
DECLARE @Sys varchar (4)
DECLARE @Prin varchar (4)
DECLARE @Account_Num varchar(16)
DECLARE @Tran_Code varchar(3)
DECLARE @Clerk_Code varchar(3)
DECLARE @Memo_Text varchar(57)
DECLARE @SubTrans varchar(2)
DECLARE @SubTrans_2 varchar(1)
DECLARE @Terminal_Id varchar(3)
DECLARE @Op_Code varchar(2)
DECLARE @Last4 varchar(6)
DECLARE @vwSys varchar (4)
DECLARE @vwPrin varchar (4)
/* Define Cursor */
DECLARE MyCursor cursor
For SELECT Batch_Type, Batch_Number, Sys, Prin, Account_Number, Transaction_Code,
Clerk_Code, Memo_Text, SubTrans, SubTrans2, Term_id, Op_Code
FROM dbo.tblNonMon_Daily_Trans
Open MyCursor; /*Works like an ARRAY*//*holds current record */
FETCH NEXT FROM MyCursor INTO @Batch_Type, @Batch_Num, @Sys, @Prin, @Account_Num,
@Tran_Code, @Clerk_Code, @Memo_Text, @SubTrans, @SubTrans_2,
@Terminal_Id, @Op_Code
Declare vwCursor cursor
FOR SELECT Sys, Prin FROM dbo.vwNonMonSysPrins
open vwCursor;
FETCH next FROM vwcursor INTO @vwSys, @vwPrin
/* When @@Fetch_Status = 1 EOF has been reached */
WHILE @@Fetch_Status = 0
BEGIN
WHILE @vwSys = @Sys
BEGIN
Any input would be appreciated.
Thanx,
Hi,I have a User-defined function "Concatenate_NoteTexts" which I use in aquery (SQL Server 2000). On my local development machine it is called likethis:SELECTdbo.Concatenate_NoteTexts(Introducers.IntroducerID ) as NoteTextsFROM tblIntroducersI want to run the same code on a shared remote server where I am user "JON"instead of "dbo". I don't want to hard-code the User Name into the SQL, butwhen I tried to put the user name into a variable as here:DECLARE @USER_NAME VarChar(30)SET @USER_NAME = USER_NAME()SELECT@USER_NAME.Concatenate_NoteTexts(Introducers.Intro ducerID) as NoteTextsFROM tblIntroducersI get the following error:Server: Msg 170, Level 15, State 1, Line 4Line 4: Incorrect syntax near '.'Any advice?TIA,JONPS First posted earlier today to AspMessageBoard - no answers yet.http://www.aspmessageboard.com/foru...=626289&F=21&P=1
View 5 Replies View RelatedI need to do something relatively simple…I need to update a table using a cursor. (I may have to create astored procedure for doing this…)I need to declare an update cursor, fetch the cursor and update thedata (and presumably close the cursor and de-allocate it…The update query is as follows… Would anyone there know how todeclare the cursor for update and use it?UPDATE ASET A.Field1 =(SELECT B.Field1FROM B INNER JOIN A ON A.id = B.id)I need to know how to declare the cursor and fetch it.Can anyone give me an example of the code I need for the SQL Server?Thanks!
View 4 Replies View Related
I am entering the following:
declare @iAbsenceCategoryID int;
declare @dEntitlementRenewal datetime;
declare @iAbsenceUserID int;
declare @iMinutesUsed int;
declare @iMinutesNotUsed int;
declare @iEntitlement int;
declare cr cursor forward_only
for
select iabsencecategoryid,
dentitlementrenewal
from TblTAAbsenceCategories
open cr;
fetch next from cr into
@iAbsenceCategoryID, @dEntitlementRenewal
WHILE @@FETCH_STATUS = 0
BEGIN
fetch next from cr into
@iAbsenceCategoryID, @dEntitlementRenewal
if (DATEPART("Month",(GETDATE()))) = (DATEPART("Month", (@dEntitlementRenewal))) and (DATEPART("Day",(GETDATE()))) = (DATEPART("Day",(GETDATE())))
declare usercr cursor FORWARD_ONLY for
select iAbsenceUserID from TblTAAbsenceUsers
where iAbsenceCategoryID = @iAbsenceCategoryID
fetch next from usercr into
@iAbsenceUserID
WHILE @@FETCH_STATUS = 0
BEGIN
fetch next from usercr into
@iAbsenceUserID
select @iMinutesUsed = sum(iMinutesRequested)
from tblTAAbsenceHolidays
where iAbsenceUserID = 13 and iAbsenceActionID != 2
select @iEntitlement = iHolidayEntitlement from TblTAAbsenceUsers where iAbsenceUserID = 13;
set @iMinutesNotUsed = (@iEntitlement) - (@iMinutesUsed);
update TblTAAbsenceUsers
set iMinutesNotUsed = @iMinutesNotUsed
where iAbsenceUserID = @iAbsenceUserID;
END;
END;
close usercr;
close cr;
DEALLOCATE usercr;
DEALLOCATE cr;
and i get the following error message:
Msg 16916, Level 16, State 1, Line 26
A cursor with the name 'usercr' does not exist.
Msg 16916, Level 16, State 1, Line 57
A cursor with the name 'usercr' does not exist.
Msg 16916, Level 16, State 1, Line 60
A cursor with the name 'usercr' does not exist.
I cant see why its not allowing me to declare the usercr cursor, can anyone tell me where im going wrong? thanks!
I am trying to use variables that are declared in a report that I am created in SSRS 2005.
For example:
Declare @Month as int
Set @Month = case when month(getdate()) = 1 then 9 else month(getdate()) - 3
When I do this, I get an error message, 'The declare cursor SQL construct or statement is not supported.'
So how do i properly declare a variable that can be used through all my dataset?
Thanks, Iris
Hello all! After I declar a variable how would I set the result of a sql query to the variable so i can utilize it further in my stored procedure?
-Thanks,
Rich
I'm attempting to use FOR XML EXPLICIT in SQLServer to adhere to the following template:
<group>
<sourceid>
<source></source>
<id></id>
<grouptype>
<scheme></scheme>
<typevalue></typevalue>
</grouptype>
<description>
<short></short>
<long></long>
</descrption>
</group>
Here's my code:
SELECT 1 as Tag,
NULL as Parent,
@p_school_desc as [sourceid!1!source!element],
@p_term_code as [sourceid!1!id!element],
NULL as [grouptype!1!scheme!element],
NULL as [grouptype!1!typevalue!element],
NULL as [description!1!short!element],
NULL as [description!1!long!element]
FROM course_main AS sourceid
where sourceid.course_id = @p_course
UNION
SELECT 1 as Tag,
NULL as Parent,
NULL,
NULL,
@p_destination as [grouptype!2!scheme!element],
'Term' as [grouptype!2!typevalue!element],
NULL,
NULL
FROM course_main AS grouptype
where grouptype.course_id = @p_course
UNION
SELECT 1 as Tag,
NULL as Parent,
NULL,
NULL,
NULL,
NULL,
@p_term_code as [description!2!short!element],
@p_term_description as [description!2!long!element]
FROM course_main AS grouptype
where grouptype.course_id = @p_course
FOR XML EXPLICIT
I'm receiving the foollowing error:
Server: Msg 6812, Level 16, State 1, Line 14
XML tag ID 1 that was originally declared as 'sourceid' is being redeclared as 'grouptype'.
Is there anyway to have two different child notes off of the same parent node?
Thanks in advance!
I'm attempting to use FOR XML EXPLICIT in SQLServer to adhere to the following template:
<group>
<sourceid>
<source></source>
<id></id>
<grouptype>
<scheme></scheme>
<typevalue></typevalue>
</grouptype>
<description>
<short></short>
<long></long>
</descrption>
</group>
Here's my code:
SELECT 1 as Tag,
NULL as Parent,
@p_school_descas [sourceid!1!source!element],
@p_term_code as [sourceid!1!id!element],
NULLas [grouptype!1!scheme!element],
NULLas [grouptype!1!typevalue!element],
NULLas [description!1!short!element],
NULLas [description!1!long!element]
FROM course_main AS sourceid
where sourceid.course_id = @p_course
UNION
SELECT 1 as Tag,
NULL as Parent,
NULL,
NULL,
@p_destinationas [grouptype!2!scheme!element],
'Term' as [grouptype!2!typevalue!element],
NULL,
NULL
FROM course_main AS grouptype
where grouptype.course_id = @p_course
UNION
SELECT 1 as Tag,
NULL as Parent,
NULL,
NULL,
NULL,
NULL,
@p_term_codeas [description!2!short!element],
@p_term_descriptionas [description!2!long!element]
FROM course_main AS grouptype
where grouptype.course_id = @p_course
FOR XML EXPLICIT
I'm receiving the foollowing error:Server: Msg 6812, Level 16, State 1, Line 14
XML tag ID 1 that was originally declared as 'sourceid' is being redeclared as 'grouptype'.
Is there anyway to have two different child notes off of the same parent node?
Thanks in advance!
hi guys i have a question ..... i would like to know how to delare a compound attribute in this case would be Birthinfo onto a sql server script. from this diagram . thanyou guyt in advance and any suggestion or help would be greatly appreciated .....
View 10 Replies View RelatedHow do I declare a cursor on a table like #TempPerson
when thhs table is only created when I do :
Select Name, Age Into #TempPerson From Person
HI,
WHILE DECLARING A CURSOR TO SELECT RECORDS FROM A TABLE WE NORMALLY WRITE :-
DECLARE CUR_NAME CURSOR
FOR SELECT * FROM CLEANCUSTOMER
BUT SAY, IF I HAVE WRITTEN A SIMPLE PROCEDURE CALLED AS MY_PROC :-
CREATE PROCEDURE MY_PROC
AS
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CLEANCUSTOMER A
INNER JOIN TRCUSTOMERPREFERENCE03JULY B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
ORDER BY B.INTPREFERENCEID
WHICH IS RUNNING FINE AND GIVING ME THE REQUIRED DATA WHILE EXECUTING THE PROCEDURE :-
EXEC MY_PROC
BUT IF I WANT TO CALL THIS PROCEDURE MY_PROC WHILE DECLARING A CURSOR :-
I AM USING :-
DECLARE CHK_CUR CURSOR
FOR SELECT * FROM MY_PROC
WHICH IS GIVING AN ERROR "Invalid object name 'MY_PROC'."
AND IF I USE :-
DECLARE CHK_CUR CURSOR
FOR EXEC MY_PROC
WHICH IS GIVING AN ERROR "Incorrect syntax near the keyword 'EXEC'".
AND IF I USE :-
DECLARE CHK_CUR CURSOR
FOR CALL MY_PROC
WHICH IS GIVING AN ERROR "Incorrect syntax near 'CALL'. "
IS THERE ANY WAY BY WHICH I CAN FETCH RECORDS FROM THE STORED PROCEDURE?
HOW DO I DECLARE THE PROCEDURE WHILE WRITING THE CURSOR
PLS HELP.
I NEED THIS URGENTLY, I HAVE TO USE THE CURSOR TO FETCH THE RECORDS FROM THE SP,THAT'S HOW THEY WANT IT.I CAN'T HELP IT AND I DON'T KNOW HOW
THANKS