Hi I Want To Know How To Write Stored Procedure ..then I Have To Include (IF Condition ) With SP ..
hi i want to know how to write stored procedure ..then i have to include (IF condition ) with SP ..
let me this post ..................anybody ??????????
View Complete Forum Thread with Replies
Related Forum Messages:
How Can I Include A Timestamp On A File Name In A Stored Procedure???
ok...i give up..... can this be done? (sql server 2000) all i want to do is have my stored procedure output some data to a file and i want the filename to include a time stamp. here's my current working file output code: EXEC master..xp_cmdshell 'bcp "select * from CEData..employee" queryout "c:employees.txt" -c -Usa -P' i'd like it to be something like this: EXEC master..xp_cmdshell 'bcp "select * from CEData..employee" queryout "c:employees" + datetime + ".txt" -c -Usa -P' but nothing seems to work.
View Replies !
If Condition With Stored Procedure
i am trying to use INSERT statement based on some condition not WHERE but using IF condition on the database. something like INSERT INTO table1 if id = @id or order = @fdfdfd. i think it will return no of rows affected with insert statement. am i right? any help will be appreciated.
View Replies !
Using Results From Stored Procedure In WHERE Condition
Hi, I'm trying to write a nested stored procedure, with the outer proc named "spAssociate", and inner proc named "spSales". So far I have created the inner proc CREATE PROCEDURE spSales@userID intASSET NOCOUNT ON SELECT SalesOppID FROM Sales_Opportunities WHERE SalesOppCurrentStatus NOT IN ('Sale Lost','Sales Closed','Sale Closed','Unqualified','Deferred','Dropped') AND OppOwnerUserID = @userIDGO This was successfully created. I wanted to use the return set in the outer proc, which I tried creating as: CREATE PROCEDURE spAssociate(@userID int, @containerType varchar(100), @associateType varchar(100))AS SET NOCOUNT ON SELECT AssociateID FROM AppRelations WHERE ContainerType=@containerType AND ContainerID IN (EXECUTE spSales @userID) AND AssociateType=@associateType GO I get an error "incorrect syntax near execute". How can I use the results from the inner proc for the WHERE condition in my outer proc? Any help is greatly appreciated. Thanks!
View Replies !
WHERE Clause' Search Condition From Argument In A Stored Procedure.
Hello I wonder if someone could suggest a way to obtain the following. Using SQL Server 2005 I want to create some stored procedures. I want to query the DB with various filter arguments and combinations of these. One way would be to create one stored procedure for each function signature. However, as the number of combinations of filter is large, if possible I'd rather have a generic input to the each stored procedure that corresponds to the entire WHERE clause' search condition. The stereotype behavior I'm looking for is: SELECT myField FROM myTable WHERE @mySearchCondition Does any one have some good suggestion, code samples and/or links? Kind regards Jens Ivar
View Replies !
How To Write This Condition?
Hi, I am trying to set up a conditional split. As usual, my books do not explain what I am trying to do :-) flat file source --> conditional split (if Column0 contains a certain string send to error file) In the Conditional Split Transformation Editor I have the following for "condition": FINDSTRING( "my search string", [Column0] ,1 ) TITLE: Microsoft Visual Studio ------------------------------ Error at myPkg [Conditional Split [8259]]: The expression "FINDSTRING( "my search string[Column0] ,1 )" is not Boolean. The result type of the expression must be Boolean. Error at myPkg[Conditional Split [8259]]: The expression "FINDSTRING( "my search string[Column0] ,1 )" on "output "Case 1" (8351)" is not valid. Error at myPkg [Conditional Split [8259]]: Failed to set property "Expression" on "output "Case 1" (8351)". As you can see I want it to search Column0. Can someone please point out my error. Thanks
View Replies !
How To Write Stored Procedure?
Hi all, I want to write a stored procedure for the following, The table have the following field, CategoryID, CategoryName, Parent_categoryID 123 Kids Dresses 0 321 Kids Boys 123 322 Kids Baby 123 401 Kids Boys school Uniform 321 501 Kids Boys Formals 321 541 Kids Baby school Uniform 322 542 Kids Baby Formals 322 601 Household Textile 0 602 Bathrobe 601 603 Carpet 601 604 Table Cloth 601 From the above example the categoryID acts as a parent_categoryID for some products, when i pass a categoryID the stored procedure should return all the categoryID which are subcategory to given categoryID, subcategory may contain some subcategory, so when i give a categoryID it should return all the subcategoryID. For example when i pass categoryID as 123 it should return the following subcategoryIDs 321,322,401,501,541,542 because these are all subcategory of categoryID 123. How to write stored procedure for this? Thanks!
View Replies !
Need To Write A Stored Procedure
Hi,I need to write a stored procedure to check if the string = "web_version" exists in another string called FromCode.The stored procedure accepts 2 parameters like this:Create proc [dbo].[spGetPeerFromCode]( @FromCode VARCHAR(50)) (@WebVersionString VARCHAR(50))as please assist.
View Replies !
How To Write Stored Procedure
Hai Friends, I heard that stored procedures can be written using two servers how to write stored procedures by using two servers CAN ANYONE GIVE INFORMATION ABT THIS Malathi Rao
View Replies !
Can Anyone Help Me To Write A Stored Procedure For The Following Scenario...
Hai friends, The project i'm working on is an asp.net project with SQL Server 2000 as the database tool. I've a listbox (multiline selection) in my form, whose selected values will be concatenated and sent to the server as comma separated numbers (Fro ex: 34,67,23,60).Also, the column in the table at the back end contains comma separated numbers (For ex: 1,3,7,34,23). What i need to do is - 1. Select the rows in the table where the latter example has atleast one value of the former example (In the above ex: 34 and 23). 2. Check the text value of these numbers in another table (master table) and populate the text value of these numbers in a comma separated format in a grid in the front end. I've programmed a procedure for this. but it takes more than 2 minutes to return the result. Also the table has over 20,000 rows and performance should be kept in mind. Suggessions on using the front-end (asp.net 2.0) concepts would also be a good help. Anybody's helping thought would be greatly appreciated... Thanks lot...
View Replies !
How To Write A Stored Procedure In SQL Server2000?
Hi all, I want to write a stored procedure for the following, The table have the following field, CategoryID, CategoryName, Parent_categoryID 123 Kids Dresses 0 321 Kids Boys 123 322 Kids Baby 123 401 Kids Boys school Uniform 321 501 Kids Boys Formals 321 541 Kids Baby school Uniform 322 542 Kids Baby Formals 322 601 Household Textile 0 602 Bathrobe 601 603 Carpet 601 604 Table Cloth 601 From the above example the categoryID acts as a parent_categoryID for some products, when i pass a categoryID the stored procedure should return all the categoryID which are subcategory to given categoryID, subcategory may contain some subcategory, so when i give a categoryID it should return all the subcategoryID. For example when i pass categoryID as 123 it should return the following subcategoryIDs 321,322,401,501,541,542 because these are all subcategory of categoryID 123. How to write stored procedure for this? Thanks!
View Replies !
How To Write Stored Procedure With Two Parameters
How to write stored procedure with two parametershow to check those variables containing values or empty in SP if those two variables contains values they shld be included in Where criteiriea in folowing Query with AND condition, if only one contains value one shld be include not other one Select * from orders plz write this SP for me thanks
View Replies !
How To Write Trigger Based On Certain Condition
One of my table called as 'customertable' contains following fields customername, emailid, subscriptionendperiod Example records are: david, david@john.com, 12/20/2005(mm/dd/yyyy format). My question is that: Just one month before subscriptionendperiod that is on 11/20/2005(mm/dd/yyyy) an automatic email should go to david@john.com with the message 'Your subscription period ends on 12/20/2005' How to write trigger for this. Please Note : No ASP code is invloved in this. Only MSSQL coding to be done. Regards
View Replies !
Could You Write A Special Stored Procedure For Me In SQL 2005?
Could you write a special stored procedure for me in SQL 2005? When I execute the SQL "select TagName from Th_TagTable where IDTopic=@IDTopic" , it return several rows such as TagName= SportTagName= NewTagName= Health I hope there is a stored procedure which can join all TagName into a single string and return,it means when I pass @IDTopic to the stored procedure, it return "Sport,New,Health" How can write the stored procedure?
View Replies !
Stored Procedure To Read And Write Between XML And SQLServer
Hi All, I need some help from you experts. I need to develop something that reads from xml files and writes in to sqlserver, also it should read from SQLServer and writes to xml. I should be able to give this as a job to exectue daily ,etc. Can we do this using stored procedures in SQLServer. Pls paste some sample code, if any or direct me to any url with better info. Thanks in advance
View Replies !
Is It Possible To Write Clr Stored Procedure With Out Using Adomd Server.
hi, I need to write a clr stored prodeure.i have to call that stored procedure from my prediction query.My Application is going to make use of that query to get the prediction output. Is it possible to write clr stored procedure with out using adomd server.How? For example: In My Assembly i am having a function PredictPerformance(),Which is having my DMX Query.I need to execute that function from my analysis service by calling like this, select aly. PredictPerformance() FROM [Model]. how to do this?
View Replies !
Could You Write A Special Stored Procedure For Me In SQL 2005?
When I execute the SQL "select TagName from Th_TagTable where IDTopic=@IDTopic" , it return several rows such as TagName= http://www.hothelpdesk.com TagName= http://www.hellocw.com TagName= http://www.supercoolbookmark.com I hope there is a stored procedure which can join all TagName into a single string and return, it means when I pass @IDTopic to the stored procedure, it return "http://www.hothelpdesk.com, http://www.hellocw.com, http://www.supercoolbookmark.com" How can write the stored procedure?
View Replies !
Write An Xml File With A Stored Procedure RESOLVED
Can you, and if yes, how do you, write an xml file using a stored procedure. For example the sp below writes this new record to a sql table. How would I change it to write it to an xml file ? CREATE Procedure [spOB_AddtblOB_Properties] @strPropertyRef varchar (100), @strRouteNo numeric, @strAdd1 nvarchar(1000), @strPostcode nvarchar(10) as Insert into tblOB_Properties ( PR_PropertyRef, PR_RouteNo, PR_Add1, PR_Postcode) values( @strPropertyRef, @strRouteNo, @strAdd1, @strPostcode) GO
View Replies !
How To Put Condition In Select Statement To Write A Cursor
col1 col2 col3 col4 36930.60 145 N . 00 17618.43 190 N . 00 6259.20 115 N .00 8175.45 19 N .00 18022.54 212 N .00 111.07 212 B .00 13393.05 67 N .00 In above 4 col if col3 value is B then cursor has to fectch appropriate value from col4. if col3 value is N then cursor has to fectch appropriate value from col1. here col2 values are unique. Can any one reply for this..............
View Replies !
How Should I Write Stored Procedure And Pass It Into Sql Server Express?
Hi i m using vwd2005 express and sql express.1. I want to write stored procedure in sql express using sql server management studio express. I m not sure where to write and how to execute the stored procedure in sql server management studio express. Can any one explain the steps required to achieve this? 2.And what is the difference between creating database using:- a. right click the project in solution explorer-> add new item->SQl database(creating .mdf file)and b.clicking a database explorer->data connection->Add connection .... thanks. jack.
View Replies !
How To Write Stored Procedure To Deal With This Kind Of Question?
I can get following data information from table1Name DescriptionA AviationA01 Aviation GeneralCA01 CapitalCA01-006 NEW CTB SignageA02 CapitalCA05-005 East End RoadwayCA05-006 Modify RoadHow do I write stored procedure to get following results based on the data from table1Code Name Description Level 1 A Aviation 1 1.01 A01 Aviation General 2 1.01.01 CA01 Capital 3 1.01.01.006 CA01-006 NEW CTB Signage 4 1.02 A02 Capital 2 1.02.01 CA05-005 East End Roadway 3 1.02.01.006 CA05-006 Modify Road 4Thank a lot!
View Replies !
How To Write Such A Stored Procedure To Return Monthly Sales?
I have a Orders and OrderDetails table having the columns listed below: Orders (OrderID, OrderDate, CustomerID, ...) OrderDetails (OrderID, ProductID, UnitPrice, Quantity, ...) Now, I want to obtain monthly sales from the data in the two tables by passing in a Year parameter. How to develop such a stored procedure? I have no idea where to get started. I was thinking to call a SELECT statement on each month. But, things trouble me are: 1. how to loop through each month to make a SELECT query for each month for a given Year? Use a cursor or what? 2. how to determine month boundary for a given year and construct the where clause on OrderDate using the month boundary for the SELECT query ? What happens if it is a leap year? 3. how to stop processing for the rest of the year when last order month is done? Any suggestion would be appreciated. Thanks.
View Replies !
How To Write One Stored Procedure For A (same Column )present In 20 Tables
hi,i am a learner of ms -sql server 2000, i had a doubt in storedproceduressuppose i have a data base having 20 tables, all the tables have acolumn named--DATEcan we write a store procdure to find out the data ---i mean i wantthe data enteredbetween two days ---- if i call the stored procedure in any one of thetable i need to get the answerpls help me how to write the stored proceduresatishkumar.g
View Replies !
Write A Stored Procedure Based On Recursive Data.
Hello, I am hoping someone can help me in this. I am looking to write a stored procedure that will return the heirarchy of an organization. I will display how the heirarchy might look and then list the tables involved. John Smith - Jacob Jones - Lisa Thompson - Samuel Barber - Paul Smith - John Jackson Ok, so Jacob, Lisa, an Samuel report up to John Smith. Paul and John Jackson report up to Samuel Barber. Here are the tables: Users holds the user_id, first_name, last_name, and reports_to_user_id. User_Roles holds the user_id, role_type_id Role_Types holds the role_type_id, and the type (which could be Administrator, Standard, Guest) for example. In addition, Role_Types also has ranking which must be taken into consideration as well. 1 being the top rank and 9 being the lowest. Thanks very much in advance, Saied
View Replies !
How To Write Condition In The Conditional Split Control In SSIS
HI i need to write the Condition for Insert and Update Reccord depending upon the Prod_ID. How to write the Follwing condtion in the Condition Split? pls Anyone give me the Solution? " if Prod_ID Exist then UPDATE Records if Prod_ID Not EXIST then INSERT Records " how to write the above conditon in the Condional Split? Thanks & Regards, Jeyakumar.M chennai
View Replies !
HELP To Write Stored Procedure Whose Values Are Calculated Automatically In Database
Hi frdz, I m creating my web-application in asp.net with C# 2005 and using sql server 2005. I have created the stored procedure for the insert,update. I want to know how to write the mathematical calculations in the stored procedure.. Pls tell me from the below stored procedure were i m making the mistake ?? As the discount and the total amount are not calculated by itself....and stored in the database How to convert the @discpercent numeric(5,2) to@discpercent ="NoDiscount" should be displayed when no discount is being given to the customers.... ALTER PROCEDURE CalculationStoredProcedure @accountid int output, @accountname varchar(20), @opbal numeric(10, 2), @opbalcode char(2), @total numeric(10, 2), @clbal numeric(10, 2), @clbalcode char(2), @discpercent numeric(5,2), @discamt numeric(10, 2) as begin set nocount on if @opbal IS NULL OR @opbal = 0 begin select @opbal=0 select @opbalcode= ' ' select @clbal= 0 select @total= 0 select @clbalcode= ' ' @discpercent ="NoDiscount" @discamt=0 end select @accountid = isnull(max(accountid),0) + 1 from accountmaster select @total=@opbal - @clbal from accountmaster select @discamt=@total* @discpercent/100 from accountmaster begin insert into accountmaster ( accountname,opbal,opbalcode,clbal,clbalcode ) values ( @accountname,@opbal,@opbalcode,@clbal,@clbalcode ) end set nocount off end Thanxs in adv...
View Replies !
Include Code In Various Stored Procedures
I have some lengthy code that creates a #Temp file. I would like to access this code from different stored procedures. Is there such a thing as an INCLUDE statement for stored procedures, or is this what the EXEC statement accomplishes?
View Replies !
Update Condition(store Procedure)
can anyone help to create storeprocedure in SQL SERVER 2000? the case is like this: i have one table call [TIMETABLE] which contains these fields:ID,start_time,finish_time,subject_code,venu e and another table call [attendant_sheet] has these following fields:ID(which should be from TIMETABLE),student_id,subject_code,status,date. and the storeprocedure will works like this: if the time(system time) has pass the finish_time on TIMETABLE table then insert the related field into attendant_sheet. and then update the status field to 'absent' and the student_id should be get it from another table call [enrolment] who havent take their attendance and enrols related subect? could this possible? PLZ HELP ME!
View Replies !
Exiting The Stored Proc On Condition
CREATE PROCEDURE [dbo].[WWspLoadData] @Planid varchar(5), @OverMar int = Null need to check here if the @PlainID exists in the tbOldCustomers.PlainID if it does exists then do nothing and exit the store procedure, and if it does not exists continue to the rest of the store procedure.
View Replies !
COndition Spli - Error Date Condition
Dear friends, I'm having a problem... maybe it's very simple, but with soo many work, right now I can't think well... I need to filter rows in a dataflow... I created a condition spli to that... maybe there is a better solution... And the condition is: Datex != NULL(DT_DATE) (Some DATE != NULL) [Eliminar Datex NULL [17090]] Error: The expression "Datex != NULL(DT_DATE)" on "output "Case 1" (17123)" evaluated to NULL, but the "component "Eliminar Datex NULL" (17090)" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row). The expression results must be Boolean for a Conditional Split. A NULL expression result is an error. What is wrong?? Regards, Pedro
View Replies !
Calling A Stored Procedure From ADO.NET 2.0-VB 2005 Express: Working With SELECT Statements In The Stored Procedure-4 Errors?
Hi all, I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE): (1) /////--spTopSixAnalytes.sql--/// USE ssmsExpressDB GO CREATE Procedure [dbo].[spTopSixAnalytes] AS SET ROWCOUNT 6 SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName FROM LabTests ORDER BY LabTests.Result DESC GO (2) /////--spTopSixAnalytesEXEC.sql--////////////// USE ssmsExpressDB GO EXEC spTopSixAnalytes GO I executed them and got the following results in SSMSE: TopSixAnalytes Unit AnalyteName 1 222.10 ug/Kg Acetone 2 220.30 ug/Kg Acetone 3 211.90 ug/Kg Acetone 4 140.30 ug/L Acetone 5 120.70 ug/L Acetone 6 90.70 ug/L Acetone ///////////////////////////////////////////////////////////////////////////////////////////// Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming: //////////////////--spTopSixAnalytes.vb--/////////// Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;") Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection) sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure 'Pass the name of the DataSet through the overloaded contructor 'of the DataSet class. Dim dataSet As DataSet ("ssmsExpressDB") sqlConnection.Open() sqlDataAdapter.Fill(DataSet) sqlConnection.Close() End Sub End Class /////////////////////////////////////////////////////////////////////////////////////////// I executed the above code and I got the following 4 errors: Error #1: Type 'SqlConnection' is not defined (in Form1.vb) Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb) Error #3: Array bounds cannot appear in type specifiers (in Form1.vb) Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1) Please help and advise. Thanks in advance, Scott Chang More Information for you to know: I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly. I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
View Replies !
How Can I Write This Store Procedure
sql result: startdate enddate price 2004-1-2 2004-1-4 677 2004-1-5 2004-1-8 900 2004-1-9 2004-1-12 2000 i want display in page this format: 2004-1-2 2004-1-3 2004-1-4 2004-1-5 ... 2004-1-12 677 677 677 900 2000 how can i write store procedure
View Replies !
How To Write Store Procedure
Hi, I am new to using store procedures Can anyone please check and tell me whats wrong in it create procedure Poll_Grid @top int, @ofset int, @orderby varchar(50) as if (@orderby = "question") select top @ofset substring(question,1,50) question, startdate, username,categoryname from polls join users on polls.userid= users.userid join category on polls.categoryid= category.categoryid where pollid not in (select top @top pollid from polls order by Substring(@orderby,1,50)) order by substring(@orderby,1,50) else select top @ofset substring(question,1,50) question, startdate, username,categoryname from polls join users on polls.userid= users.userid join category on polls.categoryid= category.categoryid where pollid not in (select top @top pollid from polls order by @orderby) order by @orderby I want to check if @orderby is passed as question it should fire the first query other wise last and also I want to dynamically insert the Ofset and top value for my paging require ment Please help..
View Replies !
How Do You Write An UPDATE Store Procedure?
I have these 3 tables that are related to each other. I am trying to change the brand name of a product and the type of the product. How can I do this? 1 CREATE TABLE Products 2 ( 3 ProductID int IDENTITY (1,1) PRIMARY KEY, 4 ProductSKU nvarchar(100) NOT NULL UNIQUE, 5 ProductName nvarchar(255), 6 ProductDescription nvarchar(MAX), 7 isInStock bit, 8 SalePrice decimal (18,2), 9 UnitPrice decimal (18,2), 10 isOnSale bit, 11 isSpecial bit, 12 isActive bit, 13 ProductRating int, 14 ProductImageBig varchar(255), 15 ProductImageMedium varchar(255), 16 ProductImageSmall varchar(255) 17 ); 18 19 CREATE TABLE Brands 20 ( 21 BrandID int IDENTITY (1,1) PRIMARY KEY, 22 BrandName nvarchar(255) NOT NULL UNIQUE 23 ); 24 25 CREATE TABLE Types 26 ( 27 TypeID int IDENTITY (1,1) PRIMARY KEY, 28 TypeName nvarchar(150) NOT NULL UNIQUE 29 ); 30 31 // The store procedure I am having trouble with. 32 33 CREATE PROC SetProductsSKU_Brand_Type 34 @ProductID int, 35 @BrandName nvarchar(255), 36 @TypeName nvarchar(150), 37 @ProductSKU nvarchar(100), 38 @ProductName nvarchar(255), 39 @isInStock bit, 40 @SalePrice decimal (18,2), 41 @UnitPrice decimal (18,2), 42 @isOnSale bit, 43 @isSpecial bit, 44 @isActive bit 45 AS 46 UPDATE Products, Types, Brands 47 SET [BrandName] = @BrandName, [TypeName] = @TypeName, [ProductSKU] = @ProductSKU, [ProductName] = @ProductName, [isInStock] = @isInStock, 48 [UnitPrice] = @UnitPrice, [isSpecial] = @isSpecial, [isActive] = @isActive 49 FROM Products prod INNER JOIN ProductsBrands pb 50 ON prod.ProductID = pb.ProductID INNER JOIN Brands b 51 ON pb.BrandID = b.BrandID INNER JOIN ProductsTypes tp 52 ON prod.ProductID = tp.ProductID INNER JOIN Types t 53 ON tp.TypeID = t.TypeID 54 WHERE prod.ProductID = @ProductID 55 AND [ProductSKU] = @ProductSKU 56 AND [ProductName] = @ProductName 57 AND [isInStock] = @isInStock 58 AND [UnitPrice] = @UnitPrice 59 AND [isSpecial] = @isSpecial 60 AND [isActive] = @isActive 61 AND b.BrandName = @BrandName 62 AND t.TypeName = @TypeName
View Replies !
How Can I Store A Stored Procedure Name For A Report In A Table And Link It To A Dataset As A Stored Procedure?
Hi! I have about 100 SSRS 2005 reports, each of which links to a stored procedure. Each stored procedure may have two, three or even four parameters, so they vary a bit. I now also have a table called ReportInfo that stores the displayable report names, the rdl file names and some additional information that displays on the header of each report. I'd like to be able to store the name of the stored procedure in that table as well and just tell the dataset to execute that stored procedure, but it isn't working the way I expected. There are two datasets with each report. The first dataset points to the ReportInfo table, where all the standard information about the report is located, including now the name of the stored procecdure to which the second dataset is supposed to link. I'm not able to point the name of the stored procedure to a field in another dataset. I can't say, for example =First(Fields!StoredProcedure.Value, "ds_ReportInfo") That gives an error I then tried setting the dataset type to Text, creating a ReportParameter called StoredProcedure (which was filled in from the first query) and then tried: Exec (@StoredProcedure) In a way, that kind of worked. I got an error message back telling me the stored procedure needed a startdate and endingdate, which are the two parameters for this parrticular stored procedure. I just don't want to have to code that into the text query. Anyway, it wasn't my intention to have to use a text-based data query. It's as much of a hassle to use the drop-down to pick stored procedure names as it is to create a long text string with two or three parameters. I just want to dynamically control the name of the stored procedure and have it act exactly as it does when I select a stored procedure from a drop down. That is, I want to be able to tell Reporting Services where to find the name of the Stored Procedure for the dataset and then see all the fields it would return and be prompted for the two, three or four parameters exactly the same way I am when I select a stored procedure from a dropdown. The reason I ask this is that we've changed the naming convention for the stored procedures for reports, and now I'm having to go back into every report and reselect the new stored procedure name. I'd really much rather have the names in a database (in case they decide to change them again) and then just have the report pull the stored procedure name from the table. But I'm not finding an easy way to do that. I wouldn't mind putting a little piece of code in each report to do this if necessary, but what I don't want to do is have every report be different. That's the problem with using the EXECUTE statement in a text-based query. Each query has to be different based on the number and content of the parameters, and I don't want that. I just want to tell Reporting Services where to find the name of the stored procedure for the dataset and then have it treated like any other stored procedure. Any suggestions? Is anyone else trying to do this? Thanks Karen
View Replies !
Want To Write Store Procedure That Takes Parameter In SQL
Some one please tell me how do I write store procedure that receives/takes parameter values I want to write store procedure which takes ID as a parameter some one tell me how do I write store procedure that takes parameter if possibel please show me example of it iam new to this thankyou maxs
View Replies !
Calling A Stored Procedure Inside Another Stored Procedure (or &"nested Stored Procedures&")
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 Replies !
Grab IDENTITY From Called Stored Procedure For Use In Second Stored Procedure In ASP.NET Page
I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck: Public Sub InsertOrder() Conn.Open() cmd = New SqlCommand("Add_NewOrder", Conn) cmd.CommandType = CommandType.StoredProcedure ' pass customer info to stored proc cmd.Parameters.Add("@FirstName", txtFName.Text) cmd.Parameters.Add("@LastName", txtLName.Text) cmd.Parameters.Add("@AddressLine1", txtStreet.Text) cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue) cmd.Parameters.Add("@Zip", intZip.Text) cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text) cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text) cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text) cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text) cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text) ' pass order info to stored proc cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue) cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue) cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue) 'Session.Add("FirstName", txtFName.Text) cmd.ExecuteNonQuery() cmd = New SqlCommand("Add_EntreeItems", Conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc) <------------------------- Dim li As ListItem Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar) For Each li In chbxl_entrees.Items If li.Selected Then p.Value = li.Value cmd.ExecuteNonQuery() End If Next Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
View Replies !
How To Write Input Param SqlInt32[] In CLR Store Procedure
hi, I want to write a CLR store procedure like this: public static void SetStateByBatch(SqlInt32[] ids) { using (SqlConnection conn = new SqlConnection("context connection=true")) { SqlTransaction tran = conn.BeginTransaction(); foreach (SqlInt32 id in ids) { try { conn.Open(); string commString = string.Format("update dbo.Notification set state=1 where noid='{0}'", id); SqlCommand comm = new SqlCommand(commString, conn); SqlContext.Pipe.ExecuteAndSend(comm); } catch { tran.Rollback(); } } tran.Commit(); conn.Close(); } } I'know that is not correct,how to resole this problem? thanks in advance
View Replies !
Learning To Write Stored Procedures
are there any tutorials online for a person wanting to learn about writing stored procedures in sql server 2000, i know sql, but want to learn more about that, as i might be getting more involved in the database side for a coming project. or any books someone could recommend.thanks.
View Replies !
Write To A File From A Stored Procedures
May i know whether i can write some text/string to a file from inside a stored procedures? Currently i can retrieve data from a query and use bcp to write it to a file but i need to add some others text to the same file. Thanks for any reply!
View Replies !
How To Write File From A Clr Stored Procedures
Hi I wrote a clr storred procedure that use fileopen, fileput an fileclose to write a report from a sql 2005 database but I don't know how to give it permissions to do its job. The Code is Imports System Imports System.Data Imports System.Data.Sql Imports System.Data.SqlTypes Imports System.Data.SqlClient Imports Microsoft.SqlServer.Server Imports Microsoft.VisualBasic Imports System.Security.Permissions Partial Public Class StoredProcedures <Microsoft.SqlServer.Server.SqlProcedure> Public Shared Sub PriceSum( ByVal intFecha as sqlInt32 ) dim strFecha as string strFecha = cstr(intFecha) Dim f As New FileIOPermission(PermissionState.none) f.AllLocalFiles = FileIOPermissionAccess.write f.Demand() FileOpen(1, "G:AuVeJAPParchivo.txt", OpenMode.Random, OpenAccess.ReadWrite, OpenShare.LockReadWrite, 10) Using connection As New SqlConnection("context connection=true") Connection.Open() dim strConsulta as string strConsulta = "declare cur_unidades cursor for select id_tda_vdp from tda_venta_departamental_vdp where date_operacion_vdp = " & strFecha & " group by id_tda_vdp order by id_tda_vdp" Dim command As New SqlCommand(strConsulta, connection) Dim reader As SqlDataReader reader = command.ExecuteReader() using reader Dim intKon as integer intKon = 0 While reader.Read() fileput(1, reader.GetSqlstring(0) & chr(0), intKon) intKon = intKon + 1 End While End Using End Using fileClose(1) End Sub End Class As you could see, the report needs a chr(0) at the end of each line, thats why I spent a lot of time learning clr/sql, but when I finally success in crate the assembly an procedure in sql 2005 it gives me this frustrating error: Msg 6522, Level 16, State 1, Procedure prueba1, Line 0 A .NET Framework error occurred during execution of user defined routine or aggregate 'prueba1': System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed. System.Security.SecurityException: at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly asm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed) at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Object assemblyOrString, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed) at System.Security.CodeAccessSecurityEngine.CheckSetHelper(PermissionSet grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandle rmh, Object assemblyOrString, SecurityAction action, Boolean throwException) at System.Security.CodeAccessSecurityEngine.CheckSetHelper(CompressedStack cs, PermissionSet grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandle rmh, Assembly asm, SecurityAction action) at StoredProcedures.PriceSum(SqlInt32 intFecha) I use Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2) I really appreciate your help, it could be a different way to do this or a simple step by step "how to grant permission" thank you very much Adolfo Ponce
View Replies !
|
TRACKER
