Need Help On Stored Procedure Using XML Related Functions
Apr 20, 2007
Hi all,
I came across the below SQLServer stored procedure, I understand that it manipulates some XML data, but am not sure what it does.. Could any one pls explain me on the below procedure,
Also, i do not see 'values' clause in the insert statement in the below procedure, is it valid to use in this way? (as per the syntax, 'values' clause is NOT optional)
CREATE PROCEDURE [dbo].[OM_addObjData]
@xml_Doc_sample varchar(max),
@objID int
I have a page which loads an article based on an ID, what i want to do now is when the article loads there will be a repeater control which holds headlines of related articles in it. My problem is how can i achieve this in terms of passing through the articleCategoryID to the stored procedure "stream_RelatedArticles". This is my stored procedure that loads the initial article; ALTER PROCEDURE [dbo].[stream_Articles] as SELECT TOP 5 articleID, articleTitle,articleDescription, articleAuthor,articleDatePublished,articleBody from Articles ORDER BY articleDatePublished And this is the page_load;if (!IsPostBack) {string strID = Request.QueryString["id"]; SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);SqlCommand command = new SqlCommand("stream_ArticlesByID", conn); command.Parameters.Add("@articleID", SqlDbType.Int).Value = Request.QueryString["id"];command.CommandType = CommandType.StoredProcedure; conn.Open();SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection); ArticleList1.DataSource = reader; ArticleList1.DataBind(); reader.Close(); } And here is my related article stored procedure;ALTER PROCEDURE [dbo].[stream_RelatedArticles]@articleCategoryID INT as SELECT articleID,articleCategoryID, articleTitle,articleDescription, articleAuthor,articleDatePublished,articleBody from Articles WHERE articleCategoryID = @articleCategoryID
I have a situation where I want to load some entities from one table lets say the table is customers and i would like to load all the customers with first name = dummy, not only this i would like to load all the orders and order details for these specific customers (these are two different separate tables) . I want all this within one stored procedure that return me three results for three different tables. Please tell me whether it is possible and how.
Can anyone explain what's wrong with this code which is supposed to populate a dropdownlist using a parametised stored procedure and SqlDataAdapter? objCmd = new SqlCommand("bs_GetActivityValueTypes", objConn.GetConnection()); objCmd.Parameters.Add("@scid", SqlDbType.Int);objCmd.Parameters["@scid"].Value = Convert.ToInt32(Request.QueryString["scorecard"].ToString()); objAdapter = new SqlDataAdapter(objCmd);objAdapter.Fill(dsInitData, "tblValueTypes"); comboResultType.DataSource = dsInitData.Tables["tblValueTypes"]; comboResultType.DataValueField = "Type_ID";comboResultType.DataTextField = "Type_Desc";comboResultType.DataBind(); comboResultType.Items.Insert(0, new ListItem("- Select a value type -", ""));Basically, if I remove the parameter from the code and the stored procedure it works fine, but when I add the parameter back I get an "Incorrect syntax near 'bs_GetActivityValueTypes'" error at line: objAdapter.Fill(dsInitData, "tblValueTypes"); This makes no sense to me because I know the stored procedure is well formed and I've used almost identical code elsewhere. All variables are declared further up the code. Someone help please!
Hi all, What is the Difference between Stored Procedure and Inline Quries...... Is Stored Procedure is faster then Inline Query... What is the professional Approch.... I m using Inline Quries in my Professional Project with Microsoft Application Block..... Someone Said me this is not a professional approch... use stored procedure instead of Inline Quries... i m so puzzled... plz guide me..... :) Thanx Sajjad
I have a list of articles stored in my sqlserver 2000 db. To get all the articles I have a stored procedure which just contains a simple select statement. When a user click to download an article, I want to increase the download count. I can do this with a separate stored procedure with an insert statement and passing in the document id, but I'm sure I could do it within one stored procedure, just not sure how. This is my current sp for getting the articles.CREATE PROCEDURE [sp_GetArticles] ASSELECT [DocumentID], [Title], [Description], [Downloads], [UploadDate], [Filesize] FROM Documents WHERE CategoryID = 1
Problem: Have to call SPs reside in 2 SQL server DBs which will return the table information, need to merge and perform sort the result and return the final result set. this is something like wrapper SP calling other SPs asynchronoulsy.
Can this be possible in SP / SQL functions to make asynchronous calls?
If yes can you please guide me to achive this.
If no, any alternative approach, or the reason why.
Hello Gurus, I have a stored procedure that gathers data from three tables and joins them, two of the tables need to have different rowcounts set, ie. pull only a certain number of rows from one table and only a certain number of rows from another table... The number of rows it should pull are stored within a table for each. Let me explain.... these tables hold Exchange storage group and mailstore data for a number of servers. Each server has a table entry with the number of child storage groups and each storage group has a table entry with the number of child mailstores. The tables get updated every two minutes via a program. I need to be able to get the most Data with the correct child counts for each server and storage group. I believe that i've found a way to do this with a stored procedure that calls a table-valued function. The table-valued function simply filters down the storage group table to it's number of storage groups, ordered by timestamp. I may be way off here, but i can't tell because both the stored procedure and function check out fine but when i execute the stored procedure it gives me the following error: Cannot find either column "dbo" or the user-defined function or aggregate "dbo.GetExchSGInfo", or the name is ambiguous.
My code is below: Stored Procedure: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
Set @SID = (SELECT ServerID FROM dbo.Servers WHERE ServerName = @ServerName) Set @top = (SELECT sum(Children) FROM dbo.ExchangeSG WHERE ServerID = @SID) Set @SGCount = (SELECT SGCount FROM dbo.Servers WHERE ServerID = @SID)
SET ROWCOUNT @top SELECT dbo.ExchangeMSData.*, dboExchangeMailStore.*, dbo.GetExchSGInfo(@SID,@SGCount) As ExchangeSG, dbo.Servers.* FROM dbo.Servers INNER JOIN ExchangeSG ON dbo.Servers.ServerID = ExchangeSG.ServerID INNER JOIN dbo.ExchangeMailStore ON ExchangeSG.StorageGroupID = dbo.ExchangeMailStore.StorageGroupID INNER JOIN dbo.ExchangeMSData ON dbo.ExchangeMailStore.MailstoreID = dbo.ExchangeMSData.MailstoreID WHERE (dbo.Servers.ServerName = @ServerName) ORDER BY dbo.ExchangeMSData.[TimeStamp] DESC, dbo.ExchangeSG.[TimeStamp] DESC
SET ROWCOUNT 0
And the Function: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
CREATE FUNCTION [dbo].[GetExchSGInfo] ( @SID INT, @SGCount INT ) RETURNS TABLE AS RETURN ( SELECT TOP (@SGCount) * FROM dbo.ExchangeSG WHERE ServerID = @SID ORDER BY [TimeStamp] )
I would like to use MatLab built-in statistical functions (beta, gamma, normal, etc.) from inside a SQLServer stored proceudre. Does anyone know if possible? (Of course, If so, where can I get documentation for doing this?)
Does anyone know how to call a stored procedure from within a SELECT statement? I know sybase has stored functions, but can not figure this out in SQL 7.0.
Example: Select A.ID, sp_Amount(A.ID,1) as 'Late Fees', sp_Amount(A.ID,2) as 'Payments' from A
I want the ability to return the "amounts" as columns, but without having to go through the hassle of dealing with temp tables or cursors.
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?
i have created the folowing function but keep geting an error.
Only functions and extended stored procedures can be executed from within a function.
Why am i getting this error!
Create Function myDateAdd (@buildd nvarchar(4), @avdate as nvarchar(25)) Returns nvarchar(25) as Begin declare @ret nvarchar(25) declare @sqlval as nvarchar(3000)
set @sqlval = 'select ''@ret'' = max(realday) from ( select top '+ @buildd +' realday from v_caltable where realday >= '''+ @avdate +''' and prod = 1 )a'
Hi allTrying to figure out what you use ms sql functions for. I understand stored procedures and how to create them. the question is what is the real purpose of a ms sql function considering everything i have read so far makes me think that there is no valid use for them. You can do almost everything that a function does but in a stored procedure. If somebody can give me a good examplle of a sql function i would appreciate it very much.thanks
I hope I didnt POST in the wrong group. If I did sorry. Anyhoo, on to my question. I have searched the forums and didnt quite find what I was looking for so here goes.. I have create a function that is supposed to return the "SCOPE_IDENTITY" from a stored procedure that updates the database. I'm kinda lost as to how to get the SCOPE_IDENTITY into the function. I have the following line in the function:
Dim retcode As Integer = cmd.Parameters.Add("@retcode", SqlDbType.Int).Direction = ParameterDirection.ReturnValue ... cmd.ExecuteReader(CommandBehavior.CloseConnection)
If I run the stored procedure by itself I get @retcode = 135 @RETURN_VALUE = 135 So i know the stored procedure works but how do I get that return value into a asp.net function?
I have a linked server and I want to execute SQL statements on that linked server which contains millions of records. so I decided to use SELECT * from OPENQUERY(RmtSrv,'SELECT * FROM RmtTbl WHERE Col = ' + @ColValue) but Unforetunatly the query is not succeeded because the OpenQuery doesn't accept parameters.
I used Scalar valued functions and built the query dynamically, but the Scalar valued functions doesn't execute dynamic SQL
ET @LinkedServerName = 'RmtSrv'
SET @RemoteTable= 'RmtTbl'
SET @Query = 'SELECT * FROM OPENQUERY({0}, ''SELECT Col1 FROM {1} WHERE Col2 = ''''{2}'''''') '
SET @Query = REPLACE(@Query, '{0}', @LinkedServerName)
SET @Query = REPLACE(@Query, '{1}', @RemoteTable)
SET @Query = REPLACE(@Query, '{2}', @Col2Value)
EXEC(@Query)
the above code is not executed because the functions doesn't execute dynamic queries.
I deleted the function above and wrote a Stored Procedure with the same code. but I can't query the stored procedure
SELECT GetColValue(Table1.Col1) FROM Table1.
where the GetColValue is the name of the stored procedure.
Do you have any solution that I can use to perform Remote SQL statements considering the performancewise and the code shall be centralized in certain stored procedures and functions
the Remote server contains millions of records and I will use it from tens of applications and databases.
I have a special problem. I use under MS SQL 2005 a user-function which i use to check rights in my system . This function is used in a subselect in my queries. Using MS SQL 2000 I have problems with this. The function itself works fine. It also works in the subselect if I use constant parameters for @matchids If I user a column from the main select I get an error message. Server: Nachr.-Nr. 170, Schweregrad 15, Status 1, Zeile 7.
Here is my function:
CREATE FUNCTION sf_MatchRights ( @uid int = 0, @matchids varchar(4000) = '', @delimiter char(1) = ',' ) RETURNS @results TABLE ( returncode int ) AS BEGIN INSERT INTO @results VALUES (2) DECLARE @value int
DECLARE cur CURSOR LOCAL FOR SELECT [value] FROM fn_Split (@matchids,@delimiter)
OPEN cur FETCH FROM cur INTO @value
WHILE @@FETCH_STATUS = 0 BEGIN
IF ( @value = @uid ) UPDATE @results SET returncode = 1 IF ( @value < 0 ) BEGIN IF (SELECT COUNT(*) AS Counter FROM krgroupusers WHERE luserid=@uid AND lgroupid=(@value*-1))>0 UPDATE @results SET returncode = 1 END FETCH NEXT FROM cur INTO @value END
CLOSE cur DEALLOCATE cur RETURN END
And here my select:
SELECT lid, dtcreation AS xTime, dtedit AS xActivity, xstitle AS xTitle, xtcomment AS xDesc, lcreatorid AS xUid, 10024 AS xAppid FROM xinnovator WHERE xinnovator.xlcontact=@contact AND (lcreatorid=@uid OR (xlcategory IN (SELECT lid FROM xinnovatorcats WHERE (SELECT returncode FROM sf_MatchRights (@uid, xinnovatorcats.xtqkmembers,','))=1)) )
If I replace xinnovatorcats.xtqkmembers with e.g. '1|2|3', then it works.
I know I can use columns from main selects in the subselect, too. But it seems I make something wrong with the syntax in sql2000 which works fine under sql2005.
I am thankful for any help.
PS: Here is the split function I use inside die MatchRights function... made it once. Dont run this with tons of rows .
CREATE FUNCTION fn_Split(@text nvarchar(4000), @delimiter char(1) = ',')
RETURNS @Strings TABLE ( position int IDENTITY PRIMARY KEY, value nvarchar(4000) )
AS BEGIN
DECLARE @index int SET @index = -1
WHILE (LEN(@text) > 0) BEGIN SET @index = CHARINDEX(@delimiter , @text) IF (@index = 0) AND (LEN(@text) > 0) BEGIN INSERT INTO @Strings VALUES (@text) BREAK END IF (@index > 1) BEGIN INSERT INTO @Strings VALUES (LEFT(@text, @index - 1)) SET @text = RIGHT(@text, (LEN(@text) - @index)) END ELSE SET @text = RIGHT(@text, (LEN(@text) - @index)) END RETURN END
Hi There, I've written an inline table-valued function in SQL such as the following:
ALTER FUNCTION dbo.GetCityByID( @CityID int) RETURNS TABLE AS RETURN( SELECT Name, Url FROM Cities WHERE (CityID = @CityID) )
suppose that Cities table includes three fields (CityID, Name, Url).
By the way I wrote a store procedure as follow:
ALTER PROCEDURE MyProcedure ( @MyID int) AS SELECT CountryID, OriginCityID, DestCityID FROM MyTable WHERE (MyID = @MyID)
The OriginCityID and DestCityID are related to CityID in Cities table. I wanna get the name and url of each city by its ID through this stored procedue by making relation to Cities table. so I call GetCityByID function in my stored procedure like this:
ALTER PROCEDURE MyProcedure ( @MyID int) AS SELECT CountryID, dbo.GetCityByID(OriginCityID), dbo.GetCityByID(DestCityID) FROM MyTable WHERE (MyID = @MyID)
this procedure dosn't work an returns error.
What's your solution for getting information from Cities table for OriginCityID and DestCityID? Thank you in advance.
Novice question. Would someone explain tell me what a view is used for? Also I am confused about the difference between a function and a stored procedure. They both seem like functions to me.
surjeet writes "Sir My question is ; Sir i am a software Engineer.I want to know about the Stored Procedures.Please Give me briefly details and examples of Stored Procedures.How to use Conditions and Literals ,How to use Procedures,Triggres,Functions in Crystal Reports and Suitable method of Data Base Design."
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)
'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.
Running into a brain problem here. I remeber reading an article a while back (2002?) on either Visual Studio Magazine or MSDN Magazine where there was a way to generate Stored Procedures from User Defined Functions. I need this information in order to do my job as it is also a way to cut down on time for this project I am trying to finish. Does anyone have the code or remeber what I am talking about. I just finished Kathleen Dollards article again on using XSLT to generate code but would really like to use the User Defined Functions.
I searched for the article on line but came up dry. Searched through all my magazines but could not find the article. Any help would be greatly appreciated. Bit of topic I guess but still relevant to the board.
Hi everyone.I'd like to know how stored procedures and table-valued functions compare when it comes to returning a resultant set of data. I know there is a link somewhere but I can't immediately find it.Thanks.
WE release our software once a week. In 1 months time we will have over 500 stored procedures in our DataBase. What we do for releases is when a stored procedure is changed, we put the Drop and Create parts of that script in our SQL Update Script. A problem comes up when Developer A changes My_StoredProc and then developer B changes the same stored procedure. Sometimes it works ok (the developer B will run the update script before changing his stored procedure. HOwever, it can happen where one Update script file has the same SP 5 times (5 drops 5 creates)... especially if over 300 SP's are getting updating in 1 release. We will always catch these on our tests, however, it's the 2 hours to fix the Test DB after we run these tests... What is the best way to manage these? We thought about putting our stored procedures into Team Foundation Server, but we don't know if that will work for us. We have 8 developers in our team. If anyone could help or give advice on this, it would be awesome. Thanks.
One advantage that I can see withh UDFs is that they are a bit a Views with parameters. You can perform joins on UDF columns (which you cannot do with a Stored Proc). You can do the same with Views but UDFs have the advantage that you restrict the number of rows with a parameterised WHERE (or HAVING) clause.
Hi to all, Can any body tell me what is the difference between Stored procedures and User Defined Functions ? In my assumption Function return a value or table, but SP doesn't return value instead of that SP use select statement or assign value to output statement. This is right?
