Hi i am not sure as u which forum to post but.. i wanted to write a stored procedure and i am stuck as to how to get the data.I have written some which i will post it below but i am not sure how should i go ahead.. These are tables...
I have a table called SourceDBF Which has the following Fields...
The relationship between the 2 tables is in PLAN_NUM and if the Source_Id = 1 i need to pull SRC1NAME , If 2 - SRC2Name and so forth..
I want to write a query that will find SOURCE_ID thats been listed in the SourceDBf table but that dont have a SRCNAME to it.. and this is my query to get the corresponding name for the SOURCE_ID and it works fine
Code Block
Select Distinct
@ClientId,
SOURCE_NUM,
(Select CASE s.SOURCE_NUM When 1 Then SRC1NAME
WHEN 2 Then SRC2NAME
WHEN 3 THEN SRC3NAME
WHEN 4 THEN SRC4NAME
WHEN 5 THEN SRC5NAME
WHEN 6 THEN SRC6NAME
WHEN 7 THEN SRC7NAME
WHEN 8 THEN SRC8NAME
WHEN 9 THEN SRC9NAME
WHEN 10 THEN SRC10NAME
WHEN 11 THEN SRC11NAME
WHEN 12 THEN SRC12NAME
WHEN 13 THEN SRC13NAME
WHEN 14 THEN SRC14NAME
WHEN 15 THEN SRC15NAME
END
FROM
PlanDBF p
Where
p.PLAN_NUM = s.PLAN_NUM
) as SourceName
FROM
SourceDBF s
But when i try to join query with this query it doesnt work
Code Block
SELECT
RowNumber,
'Source.Dbf, Plan.Dbf',
'Source Name is missing for Source Number "' + IsNull(RTrim(f.SOURCE_NUM),'Unknown') + '" in Plan.Dbf table.'
FROM
SourceDbf f
I am trying to get SOURCE_ID which do not hae a SRCName.
Hi, I am trying to Implement Multi parameter... If i give NULL it works fine but if i give '7,4' I get this error message Msg 102, Level 15, State 1, Line 18 Incorrect syntax near '17'. This is my sproc ALTER Procedure [dbo].[usp_GetOrdersByOrderDate] @ClientId nvarchar(max)= NULL, @StartDate datetime, @EndDate datetime AS Declare @SQLTEXT nvarchar(max) If @ClientId IS NULL Begin Select o.OrderId, o.OrderDate, o.CreatedByUserId, c.LoginId, o.Quantity, o.RequiredDeliveryDate, cp.PlanId, cp.ClientPlanId FROM [Order] o Inner Join ClientPlan cp on o.PlanId = cp.PlanId Inner Join ClientUser c on o.CreatedByUserId = c.UserId WHERE --cp.ClientId = @ClientId --AND o.OrderDate BETWEEN @StartDate AND @EndDate ORDER BY o.OrderId DESC END ELSE BEGIN SELECT @SQLTEXT = 'Select o.OrderId, o.OrderDate, o.CreatedByUserId, c.LoginId, o.Quantity, o.RequiredDeliveryDate, cp.PlanId, cp.ClientPlanId FROM [Order] o Inner Join ClientPlan cp on o.PlanId = cp.PlanId Inner Join ClientUser c on o.CreatedByUserId = c.UserId WHERE cp.ClientId in (' + @ClientId + ') AND o.OrderDate BETWEEN ' + Convert(varchar,@StartDate) + ' AND ' + convert(varchar, @EndDate) + ' ORDER BY o.OrderId DESC' execute (@SQLTEXT)
Hi, I want to write a sproc whose main table is Order and it inner joins another table called OrderShipTo with a one to many relationship so the way i have written the first query is as follows SELECT o.OrderId, o.OrderDate, o.PlanId, o.CreatedByUserId, o.Quantity, o.BillToCompany, o.BillToContact, o.BillToAddress, o.BillToCity, o.BillToState, o.BillToZip, o.BillToEmail, o.BillToPhone, o.ShipToIsBillTo, o.BookTypeId, o.RequiredDeliveryDate, o.SpecialInstructions, o.ApprovedBy, o.ApprovedByPhone, o.ApprovedByEmail, os.ShipToId, os.ShipTypeId, os.ShipToCompany, os.ShipToContact, os.ShipToAddress, os.ShipToAddress1, os.ShipToCity, os.ShipToState, os.ShipToZip, os.ShipToEmail, os.ShipToPhone, os.Quantity FROM [Order] o Inner Join OrderShipTo os on o.OrderId = os.OrderId WHERE o.OrderId = @OrderId So suppose if i give an OrderId of 15 In the OrderShipTo table they may be 3 Or 2 or none entries for this order in the Ordership to Table So if there is 3 entries in the OrderShip i will get 3 rows of data which is correct... But i just want o.Columns to appear once and i want n rows for the os.Columns to appear. So in Order To acheive i wrote this query DECLARE @OrderId int SET @OrderId = 311 SELECT o.OrderId, o.OrderDate, o.PlanId, o.CreatedByUserId, o.Quantity, o.BillToCompany, o.BillToContact, o.BillToAddress, o.BillToCity, o.BillToState, o.BillToZip, o.BillToEmail, o.BillToPhone, o.ShipToIsBillTo, o.BookTypeId, o.RequiredDeliveryDate, o.SpecialInstructions, o.ApprovedBy, o.ApprovedByPhone, o.ApprovedByEmail FROM [Order] o WHERE o.OrderId = @OrderId EXEC usp_OrderShipToLoadByOrderId @OrderId
But at the end i want all the rows o.Columns + the rows from os.columns... I also tried giving a union of both the tables but that didnt work of type mismatch.. How can i acheive that??? Regards Karen
When i try to run this sproc no rows are are returned but if i give PlanId = 898 and PortfolioId = 1913 i should get one row.. This is my sproc ALTER PROCEDURE [dbo].[rpt_Custom_AllocationsSub] ( @PlanId int, @PortfolioId int ) AS SELECT c.PlanId, c.PlanName, pp.PortfolioId, pp.PortfolioName, pp.PortfolioDescription, f.FundId, p.displayOrder, case When pf.PlanFundDisplayName IS NULL THEN f.ShortName ELSE pf.PlanFundDisplayName END FundNames, CAST(p.AllocationPercent AS integer) AS PPF_Percent FROM Fund f INNER JOIN PlanPortfolioFund p ON f.FundId = p.FundId
INNER JOIN PlanFund pf on f.FundId = pf.FundId RIGHT OUTER JOIN [ClientPlan] c INNER JOIN PlanPortfolio pp ON c.PlanId = pp.PlanId ON p.PortfolioId = pp.PortfolioId WHERE c.PlanId = @PlanId ANDp.PortfolioId = @PortfolioId AND pf.PlanId = pp.PlanId order by p.DisplayOrder This is my table structure ClientPlanId PlanId int indentity PlanFund -- PlanId int, FundId int PlanPortfolio -- PortfolioId int, PlanId int, PortfolioName varchar , PortfolioDescription varchar PlanPortfolioFund PortfolioId int, FundId int, AllocationPercent int.
Hi, I am trying to Implement Multi parameter... If i give NULL it works fine but if i give '7,4' I get this error message
Msg 102, Level 15, State 1, Line 18
Incorrect syntax near '17'.
This is my sproc
Code Block ALTER Procedure [dbo].[usp_GetOrdersByOrderDate] @ClientId nvarchar(max)= NULL, @StartDate datetime, @EndDate datetime AS Declare @SQLTEXT as nvarchar(max) if @ClientId is null Begin Select o.[OrderId], o.[OrderDate], o.[CreatedByUserId], c.LoginId, o.[Quantity], o.[RequiredDeliveryDate], cp.PlanId, cp.ClientPlanId, cp.ClientId FROM [Order] o Inner Join ClientPlan cp on o.PlanId = cp.PlanId and o.CreatedByUserId = cp.UserId Inner Join ClientUser c on o.CreatedByUserId = c.UserId WHERE --cp.ClientId = @ClientId --AND o.OrderDate BETWEEN @StartDate AND @EndDate ORDER BY o.OrderId DESC END ELSE BEGIN SELECT @SQLTEXT = 'Select o.[OrderId], o.[OrderDate], o.[CreatedByUserId], c.LoginId, o.[Quantity], o.[RequiredDeliveryDate], cp.PlanId, cp.ClientPlanId, cp.ClientId FROM [Order] o Inner Join ClientPlan cp on o.PlanId = cp.PlanId and o.CreatedByUserId = cp.UserId Inner Join ClientUser c on o.CreatedByUserId = c.UserId WHERE cp.ClientId in (' + @ClientId + ') AND o.OrderDate BETWEEN ' + Convert(varchar, @StartDate) + ' AND ' + convert(varchar, @EndDate) + ' ORDER BY o.OrderId DESC' execute (@SQLTEXT) END
Hi, I have Table called performance where i what to get the AsOfDate(char 10) from that table... and i want to get the most recent based on this function. ALTER FUNCTION [dbo].[udf_Quarter] (@Date datetime) RETURNS char(10) AS
BEGIN
DECLARE @month int SET @month = Month(@Date) RETURN CASE WHEN @month BETWEEN 4 AND 6 THEN 5 WHEN @month BETWEEN 7 AND 10 THEN 6 WHEN @month BETWEEN 11 AND 12 THEN 7 WHEN @month BETWEEN 1 AND 3 THEN 8 END END
and i want this to return 3/31/2008 but i want to make it dynamic like if it is "3/31' and year can be depending on the year it passes
I debug SPS on a daily basis and I use SQL profiler to help me trace where the problem is. Once I have established which SP is the main problem I need to debug the line of code.
What I do is Cut and Paste the SQL Profiler details and populate all the parameters,sometimes that can be 30 and more..
Now what i thought is to write an SP or Function where I pass :
SP name and Parameters that profiler genererates and returns me Declare Statements and Set Statements with parameters filled.
EG
Profiler Returns Customer_INSERT,20,'JO',BLOGG','5 LONDON ROAD'
I would call my new SP =PopulateSPParams and cut and paste the profiler's string PopulateSPParams 'Customer_Insert,20,'JO',BLOGG','5 LONDON ROAD'
this will RETURN THE FOLLOWING THAT WILL IMMENSILY HELP MY DAILY PROGRAMMING.
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?
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
********************************************* 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??
This sproc seems to be way over my head. First off, let's start with the scenario. I have two tables. tblInventory and tblTempCart. Each contain an ItemID and Quantity. I need an sproc that will loop through the rows in tblTempCart and sum the quantity of each ItemID. Then, it needs to update the quantity in tblInventory based on what has been ordered for that ItemID. What I have tried thus far: UPDATE dbo.[4HCamp_tblStoreInventory]SET Quantity = Quantity - (SELECT SUM(dbo.[4HCamp_tblStoreTempCart].Quantity) AS Quantity FROM dbo.[4HCamp_tblStoreTempCart] WHERE dbo.[4HCamp_tblStoreTempCart].ItemID = dbo.[4HCamp_tblStoreInventory].ItemID) This works other than if the ItemID doesn't exist in tblTempCart, then it updates the quantity in tblInventory to NULL instead of retaining it's current value. I have no experience with looping in sql so any help will be greatly appreciated. Thanks! Amanda
I am trying to design a stored procedure to list out all of the unique software items that have been approved. There are multiple tables involved: CISSoftware, Software, Manufacturers, SoftwareTypes. Despite putting DISTINCT, I am still receiving rows of records where the software title (the title field) is a duplicate. Why is this query not working? Am I overlooking something? SELECT DISTINCT CISSoftware.SoftwareID, Software.Title, Manufacturers.ManufacturerID, Manufacturers.ManufacturerName, SoftwareTypes.SoftwareTypeID, SoftwareTypes.Type
FROM CISSoftware, Software, Manufacturers, SoftwareTypes
WHERE CISSoftware.SoftwareID = Software.SoftwareID
AND Software.ManufacturerID = Manufacturers.ManufacturerID
AND Software.SoftwareTypeID = SoftwareTypes.SoftwareTypeID
I'm trying to learn using sproc in ASP.NET, but ran into problems I couldn't solve. Here're the details
My Table (JournalArticle) ArticleID - int (PK) ArticleTitle - varchar ArticleContent - text
I could run a normal sql string against the table itself in ASP.NET and got the results I expect. but when using a sproc, i couldn't get anything The sproc
CREATE PROCEDURE dbo.sp_ArticleSearch(@srch text) AS SELECT ArticleID, ArticleTitle, ArticleContent FROM dbo.JournalArticle WHERE (ArticleAbstract LIKE @srch) GO
After reading some of the threads here, I experimented by changing ArticleContent and @srch to type varchar, still no luck, it's not returning anything. I think the problem is when i set the value of @srch (being new at this, I could be seriously wrong though), like this:
I have tried to mix this around every way I can think of but the procedure inserts two rows instead of one. You will notice that I specify two commands/sprocs. I did that as part of my trying everything. when it was one command/sproc it did the same thing... What am I doing wrong? Please Help! :)
___________________
SPROC: ___________________ CREATE PROCEDURE dbo.sp_addMembershipRole @INCID Int AS declare @literal NVarChar (10) SET @literal = 'RTRListing'
INSERT INTO dbo.RTR_memberPermissions ([memberID], [Role]) VALUES (@INCID, @literal) GO ___________________
CODE: ___________________ using System; using System.Collections; using System.ComponentModel; using System.Data; using System.Data.SqlClient; using System.Drawing; using System.Web; using System.Web.SessionState; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.HtmlControls; using System.Security.Cryptography; using System.Web.Security;
#region Web Form Designer generated code override protected void OnInit(EventArgs e) { // // CODEGEN: This call is required by the ASP.NET Web Form Designer. // InitializeComponent(); base.OnInit(e); }
/// <summary> /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary> private void InitializeComponent() { this.btnAdd.Click += new System.EventHandler(this.btnAdd_Click); this.Load += new System.EventHandler(this.Page_Load);
} #endregion
public void btnAdd_Click(object sender, System.EventArgs e) { int intContactID; string StrCID = Session["CID"].ToString(); intContactID= Int32.Parse(StrCID);
if(password.Text != retype.Text) { lblError.Text = "Retyping of your desired password did not match. Please try again."; return; }
create procedure GetAddress(@Addr1 varchar(40), @Addr2 varchar(40), @City varchar(30), @State char(2), @Zip5 char(6), @Zip4 smallint) as begin declare @ZipID integer declare @AddrID integer set @AddrID=1 if lTrim(@Addr1)<>''
EXEC @ZipID= dbo.GetZipID(@City,@State,@Zip5)
set @AddrID = (select Min(lngAddrID) from dbo.Addrs where lngZipCodeID=@ZipID and Address1=@Addr1 and Address2=@Addr2) return(@AddrID) end GO
In the above sproc I m trying to call another sproc GetZipID . Its giving me an error stating that
"Incorrect syntax near @City. "
Can you help me out? The same syntax works for passing one variable but not for three.
FYI this is the other sproc
CREATE PROCEDURE dbo.GetZipID(@City varchar(30), @State char(2), @Zip5 char(6)) AS BEGIN 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
I wanted to know if its possible to do this in a sproc.
if you want to hide the column that has no data, I suggest you to handle these works in your data accessing modular. For example, if you check one of your column is empty, just remove the column in your record set, so the column would not show in the report.
How can I determine when a sproc or table was last used? I suspect that I have many obsolete tables and sprocs in my database but how can I find out for sure?? Thanks, DL
I wrote the following SPROC and it works the first time i run it. But if I attempt to run it again I get the following T-SQL Error: "There is not enough memory to complete the task. Close down some operations and try again". Then the app closes. Any ideas?
Here is my complete code:
USE IADATA IF EXISTS (select * from syscomments where id = object_id ('TestSP')) DROP PROCEDURE TestSP
GO CREATE PROCEDURE TestSP /*Declare Variables*/ @ListStr varchar(100) /*Hold Delimited String*/ AS Set NoCount On DECLARE@ListTbl Table (InvUnit varchar(50)) /*Creates Temp Table*/ DECLARE@CP int /*Len of String */ DECLARE @SV varchar(50) /*Holds Result */
While @ListStr<>'' Begin Set @CP=CharIndex(',',@ListStr) /*Sets length of words - Instr */ If @CP<>0 Begin Set @SV=Cast(Left(@ListStr,@CP-1) as varchar) /*Copies Portion of String*/ Set @ListStr=Right(@ListStr,Len(@ListStr)-@CP) /*Sets up next portion of string*/ End Else Begin Set @SV=Cast(@ListStr as varchar) Set @ListStr='' End Insert into @ListTbl Values (@SV) /*Inserts variable into Temp Table*/ End
Select InvUnit From @ListTbl LT INNER Join dbo.Incidents ST on ST.Inv_Unit=LT.InvUnit
and my VB6 Code:
Dim adoConn As ADODB.Connection Dim adoCmd As ADODB.Command Dim adoRS As ADODB.Recordset Dim strLegend As String Dim strData As String
Set adoConn = New ADODB.Connection adoConn.Open connString
Set adoRS = New ADODB.Recordset Set adoCmd = New ADODB.Command
I'm optimizing an Access mdb to run in front of a SS 2005 database. My approach is to move as much of the processing as possible out of Access and in to SQL Server.
I have a report uses an Access query as it's source. One field in that report is generated via a series of 4 or 5 sub-queries that are finally joined in to the report's source query.
I have enough knowhow to turn each individual Access query into a veiw inside SQL server, but I'm wondering if this wouldn't be better accomplished using a stored procedure?
Essentially, I'd need the sproc to open up a set of 50-60 records, loop through them until it finds the first record with certain criteria, then return a certain value as it's result. Finally, I need that vallue to be joined to a view that I will point to as the source for my report in Access.
Is it possible to do this with a sproc? Is this the right way to use a sproc?
Hi SQLersI'm in an unusual position of using a SQL box I'm not an admin of. To make it worse, it is SQL 2k. I haven't used this in anger for over a year. I don't even have the 2k tools installed and I'm just using SSMS.sp_adduser ain't working. I've nosed around in master and the reason why is somebody, for some reason, has created a sproc called (you guessed it) master.dbo.sp_adduser.I can't poke around as I would like and I don't have a 2k instance to bugger about with. Correct me if I am wrong but if there is some user defined master.dbo.sp_adduser sproc then there can't be a system sproc to add users? CREATE USER is SQL 2k5 too right? Users do get created by EM by the team managing the box so what command does this use? Anyone know or (flutters eyelashes) fancy running a profiler trace on EM to see?I want to convert my SQL 2k5 security objects script for SQL 2k. I can create the objects in SSMS so not critical."Overriding" system sprocs is not something I would ever do and I have no equivalent testing environment so any help highly appreciatedCheers
CREATE proc dbo.sp_address ( @Abbr char(2) ) as DECLARE @StateID int SET @Abbr = UPPER(ISNULL( @Abbr, '' )) SET @StateID = ( SELECT MIN(lngStateID) FROM dbo.States where strAbbr = @Abbr ) set @StateID=53 IF ( @StateID is null ) INSERT into dbo.States( strAbbr, strName ) VALUES( @Abbr, @Abbr ) if @@ERROR = 0 SET @StateID = @@Identity return(@StateID) GO
Can I execute the above stored procedure in a function like this:
create function sf_GetStateID( @Abbr char(2)) returns integer begin declare @StateID int exec sp_address return(@StateID) end
The probl is while inserting the fourth column values I have to execute a sproc "GetAddress" and take the value and insert it in "lngHomeID" and the the same value in "lngMailID". I tried all possible ways for the syntax but 'm getting the error incorrect sytanx near exec and also syntax error near " . " at H.strAddress1 etc......
FYI this is the sproc GetAddress(I omitted few lines in this sproc)
CREATE procedure dbo.GetAddress(@Addr1 varchar(40), @Addr2 varchar(40), @City varchar(30), @State char(2), @Zip5 char(6), @Zip4 smallint) as begin ------------------------------------------ ------------------------------------------ -------------------------------------- EXEC @ZipID= dbo.GetZipID @City,@State,@Zip5 return(@AddrID) print (@AddrID) end GO
wondering if anybody can help me, i've writen a stored procedure to query my database, i want to be able to added a wildcard into a parameter incase i don't want to use it.
(don't laugh at my efforts!!!!:)) i've tried
IF @<PARAM> = NULL @<PARAM> = LIKE %
I sure this is a simple thing to add basically i need to replace a null with everything!!
I'm trying to create a stored procedure on my master database that I can query other databases. The reason behind putting it on the master db is so I will have one place to modify the stored procedure instead of potentially a hundred different databases. I am trying to create a stored procedure like the one below, but it seems to not like it and I get the error below. Any suggestions are appreciated.
DECLARE @uName AS nvarchar(30), @dbName as nvarchar(5) SET @uName='TU' SET @dbName = '00000' DELETE FROM [DASHDB].[dbo].users WHERE Username=@uName EXECUTE('USE ['+@dbName+']') DELETE FROM DashboardBranches WHERE Username=@uName DELETE FROM MyDashboardSettings WHERE Username=@uName DELETE FROM MyFavorites WHERE Username=@uName DELETE FROM MyModules WHERE Username=@uName DELETE FROM MyStocks WHERE Username=@uName DELETE FROM BoardPackage WHERE PackageID IN (SELECT ID FROM BPackages WHERE Username=@uName) DELETE FROM BPackages WHERE Username=@uName
ERROR: Msg 208, Level 16, State 1, Line 6 Invalid object name 'DashboardBranches'.
HiI can't seem to get this procedure to work correctly. Here's my code:DECLARE @PropertyDetails nvarchar (6)Select @PropertyDescription = PropertyDescription from Property whereApplicationID = @applicationidIf @PropertyDescription is not null or @PropertyDescription <> ''BeginIf (select isnumeric(PropertyDescription) from Property where ApplicationID =@applicationid) = 1INSERT INTO #errors VALUES (1410,@ApplicationID, 0, 'Y')ELSEINSERT INTO #errors values (1410, @ApplicationID, 0, 'N')EndI am trying to bring up an error advising users not to capture alphabets in afield called Property Description.I need to bring up the error from the #ERRORS table with the rule number 1410.My Syntax checks successful, but my error does not come up for the users. AmI missing something?Thanks for any help at all, guys.Driesen Pillay--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200508/1
Hi guysI having trouble with this sproc. I get the following error when testing:Server: Msg 245, Level 16, State 1, Procedure UTL_CompletenessCheckLoan, Line231Syntax error converting the varchar value 'danwet w125 ' to a column of datatype int.I have declared @Sellername as Varchar. Please can someone tell me what I'mdoing wrong? All it needs to do is return the 'Y' value if there are numericsin the sellername.declare @sellername varchar(40),@applicationid INTselect @sellername = sellername from Seller where ApplicationId =@ApplicationIdIf @sellername is not null or @sellername <> ''beginif (select isnumeric(Sellername) from seller where ApplicationId =@ApplicationId) = 1select 'Y'ELSEselect 'N'endThanks for any helpDriesen--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200509/1
HiI am doing a check and on failing, i have a raiseerror command.I was assuming, once proc reaches raiseerror, it would stop the proc,but i see that the proc gives me an error message, but continues torun thru all the rest of the steps after raiseerror.how do i make the proc quit after reading raiseerror, only thrulabels ?IF @CheckIFFileHasOnlyOneOutputType 1BEGINRAISERROR ('Process Stopped. Input xls file is invalid, as it has morethan one output type specified in the OutputType Column', 16, 1)END--more proc steps are here ...Thanks in advance :RS
I am trying to create a sproc that willl add a row to a table. I also want to be able to test the sproc by inserting literal values and executing it. Below is the sproc that I have created. I am not sure if I have done the last part with the values correctly. The names used in the VALUES portion are the same as the variables in my c# app. I want to be able to pass the alues from my c# app to this using these variables.
I have tried to add this a row to the table by changing the word CREATE to EXEC and then running it. I also changed the values to a value with quotes around it. I thought that might make the value a literal. I did this in an effort to test the sproc. I had no luck trying to write to the database this way.
It appears to be impossible to create an xml schema in a stored procedure and then use it in a temporary table in the stored procedure. Did anyone else experience this? I am using this stored procedure:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[validate_data]
@variable_uid int,
@xml_schema xml,
@flag int OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @schema xml
set @flag = 0
create xml schema collection dbo.xsch AS @xml_schema
CREATE TABLE temp_row_values
(
row_value_uid int,
xml_fragment xml (dbo.xsch) not null
)
drop xml schema collection dbo.xsch
drop table temp_row_values
END
It produces this error message:
Msg 6314, Level 16, State 1, Procedure validate_data, Line 22
Collection specified does not exist in metadata : 'dbo.xsch'
