Can I use the result of a scalar function as the parameter for a stored procedure? ie
exec [dbo].[usp_insert_into_table]
@integer = [dbo].[uf_getAnIDfromName]('PLAYER')
where the @integer parameter expects an integer and the user function uf_getAnIDFromName returns an integer related to the 'PLAYER' name.
I developed a search stored proc that searches all orsome of Procs, Views, Triggers and functions. Would anyone be interestedto see it posted here?Do you have any suggestions about other places, websites forums ...., toshare something I have developed?Thanks you for your response in advancePLease send me emailJoin Bytes! {Remove ### before responding}
View 1 Replies View RelatedJust wondering if anyone knows of a useful command to assign execute permissions to a batch of stored procs to a user/role. I've got too many stored procs to manually go thru the steps of browsing for them and scrolling thru each one and clicking "execute" for each one.
Also, would like to know if its possible to update a batch of stored procs that begin with a prefix like "spSomething_".
Any info would be helpful! TIA.
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.
Hello all:
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.
Thanks
Hi All,
My question is :
Why we are using udf inside stored procedures ?
Will it make any performance faster for the stored procedure to execute ?
awaiting your reply.
Thanks
Renjith
lokesh writes "1) What are the differences between "Stored Procedure" and "User-Defined Functions"?
2) Places where we use/don't use Stored Procedure/User-Defined Functions."
What are the pros and cons of each?
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.
Is there a way to namespace groups of stored procs to reduce confusion when using them?
For C# you can have ProjectName.ProjectSection.Classname when naming a class. I am just wondering if there is a way to do the same with SQL stored procs. I know Oracle has packages and the name of the package provides a namespace for all of the stored procs inside it.
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?
View 3 Replies View RelatedGreetings,
We have recently begun using transactional replication to keep the data in our SQL Servers synchronized in a geographically dispersed environment. We replicate our tables but we have never replicated views, stored procedures, or user functions in our production systems. We are thinking of doing so but wonder if the overhead of running the replication agents doesn't outweigh the benefits of having replication assist with the occassional change to these design elements.
Is anyone on this forum replicating views, sprocs, and user functions? What has your experience been?
Thanks for any ideas that you share.
BCB
I have a set of reports that run just fine with the default parameters (Country = US). The report returns data within 60 seconds. However, if I change the default parameters, say to Country = UK, the report will run and won't seem to stop. The user will be prompted every few minutes to relogin to the domain (which they are not prompted when they first run the report). On the server, the report is taking up 1 of the four CPU's and is using a huge amount of disk paging.
Here's the kicker. If I go in, change the default parameters to Country = UK and deploy the report, it will run in 60 seconds with the new default parameters. Now I try to run the report by changing the country = US and it locks up when it is executed.
Anybody run into something like this?
Rob
HI,
CREATE PROCEDURE PROC1
AS
BEGIN
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CUSTOMER A
INNER JOIN CUSTOMERPREFERENCE B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
WHERE B.INTPREFERENCEID IN (6,7,2,3,12,10)
ORDER BY B.INTCUSTOMERID
END
IF I AM USING THIS PROC AS I/P TO ANOTHER PROC THEN IT GIVES NO PROBLEM AS I CAN USE ?
CREATE PROCEDURE PROC2
AS
BEGIN
CREATE TABLE #SAATHI(INTCUSTOMERID INT,CHREMAIL NVARCHAR(60),INTPREFERENCEID INT,CHRPREFERENCEDESC NVARCHAR(50))
INSERT INTO #SAATHI
EXEC PROC1
ST......1,
ST......2,
END.
BUT IF , I USE ANOTHER PROC SIMILAR TO THE FIRST ONE WHICH GIVES SLIGHTLY DIFFERENT RESULTS AND GIVES TWO SETS OF RESULTS,THEN WE HAVE A PROBLEM,HO TO SOLVE THIS :-
CREATE PROCEDURE MY_PROC
AS
BEGIN
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CUSTOMER A
INNER JOIN CUSTOMERPREFERENCE B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
WHERE B.INTPREFERENCEID IN (23,12,10)
ORDER BY B.INTCUSTOMERID
END
SELECT A.INTCUSTOMERID,MAX(case when A.intpreferenceid = 23 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 12 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 10 then '1'
else '0' end) AS PREFER
FROM CUSTOMER
GROUP BY A.INTCUSTOMERID
ORDER BY A.INTCUSTOMERID
END
WHICH NOW GIVES ME TWO SETS OF DATA , BOTH REQUIRED THEN HOW TO USE ONE SET OF RESULTS AS I/P TO ANOTHER PROC AND OTHER SET OF RESULTS AS I/P TO YET ANOTHER PROC .
CREATE PROCEDURE PROC2
AS
BEGIN
CREATE TABLE #SAATHI(INTCUSTOMERID INT,CHREMAIL NVARCHAR(60),INTPREFERENCEID INT,CHRPREFERENCEDESC NVARCHAR(50))
INSERT INTO #SAATHI
EXEC MY_PROC
ST......1,
ST......2,
END.
BUT, HERE I WANT TO USE FIRST DATASET ONLY , HOW TO USE IT ?
THANKS.
This is not obvious to me...
As far as i can tell, you cannot pass an array (or structured) parameter to a stored procedure...
Ok, this means when you have to store data for an item and its sub-items (e.g. a product and its - say- version specific infos) you cannot code all the logic into a single procedure. You need to code it into your DAL, where you first insert then loop to sub-insert...
Is this correct?
Is there any other way to approach the problem?
Thanks a lot. -julio
Hi all!Running the code below in SQL-analyzeer (or through dbExpress) results in NULL.As one might guess I would like the result to be 1. What is wrong? I.e, whywont the result of the SP come back to the caller?CREATE PROCEDURE test@val INTEGER OUTASSELECT @val = 1GODECLARE @val INTEGEREXEC test @valSELECT @val
View 1 Replies View RelatedHi all,I'd like to put together a SQL statement that will take the name of astored procedure as a param, and return that SP's parameters.I'm writing a test application, and I'd like to wrte a generator tosave myself some time, but I can't seem to figure out how to get theparams from a SP. Any help would be appreciated.Thanks in advance,Craig
View 2 Replies View RelatedAll,
I have the following :
ALTER PROCEDURE [dbo].[sp_FindNameJon]
@NameName varchar(50)='',
@NameAddress varchar(50)='',
@NameCity varchar(50)='',
@NameState varchar(2)='',
@NameZip varchar(15)='',
@NamePhone varchar(25)='',
@NameTypeId int=0,
@BureauId int,
@Page int=1,
@Count int=100000
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @SqlString nvarchar(3000),@SelectClause nvarchar(1000), @FromClause nvarchar(1000),@WhereClause nvarchar(1000)
DECLARE @ParentSqlString nvarchar(4000)
DECLARE @Start int, @End int
INSERT into aaJonTemp values (@Page, 'here2', @NameCity);
And inside of aaJonTemp, I have the following :
NULL
here2
NULL
NULL
here2
NULL
How is this possible? If @Page or @NameCity is NULL, how come it doesn't default to a value in the stored proc?
Thx
jonpfl
I have a stored proc that I'm using to run a report. It works fine, but currently I'm using a parameter that is a single selection from a dropdown. I'd like to use multi select, but have not been able to get it to work.
In the data tab I'm currently using "text" for command type and :
Code Snippet
declare @sql nvarchar(2000)
set @sql = '
EXEC [Monitor] '' + @p_OfferStatus + '''
exec sp_executesql @sql, N'@p_OfferStatus VARCHAR(100)', @p_OfferStatus = @p_OfferStatus
when I run this in the data tab, it works fine, returning data, but when I try to preview it it tells me there are syntax errors. Anyone know the correct way to use multi selects with stored procs?
I have an SP like this (edited for brevity):
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TESTING_SP]
@Username MediumText,
@Password MediumText,
@UserKey int OUTPUT,
@RoleKey int OUTPUT,
@UserGroupKey int OUTPUT,
AS
BEGIN
SELECT
@UserKey = UserKey
FROM UserProfile
WHERE Username = @UserName
AND [Password] = @Password
END
I want to execute this sp in Managment Studio (MS) and see what is being returned but I'm getting this error:
Msg 201, Level 16, State 4, Procedure TESTING_SP, Line 0
Procedure 'TESTING_SP' expects parameter '@UserKey', which was not supplied.
How do I set up the output parameters and then select the values in MS for testing purposes?
Thanks a ton for helping a noob.
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
almost choked when i read the following recent post on The Daily WTF (http://thedailywtf.com/) the other day --
Logical Tiers? That Makes No Sense! (http://thedailywtf.com/ShowPost.aspx?PostID=28959)
i don't think i can do justice to how utterly stupid that stored procedure is
read the comments and have a laugh
one of the points made was that stored procedures are bad
this twigged something in my memory, so i dug around in my bookmarks, and sure enough, here's another decent discussion about stored procs --
Stored procedures are bad, m'kay? (http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx)
enjoy!
Hey guys.
I am wondering if it is possible to execute an .exe from a stored proc. I have an application that calls a stored proc and I am wanting for that stored proc to call up an app to show certain results. I have no way of doing this on the application itself as I dont have the source code. So, since I do have access to the SQL I am wondering if it could be done there.
Thanks
tibor
First off, this is a cross post which is also located here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=34073
Now, with the following stored procs and UDF, the Result is NULL and I cannot figure out why.
What I am doing is calling a stored procedure pass in an OUTPUT variable of type float. This stored proceudre then calls a different stored procedure passing the same OUTPUT variable of type float. The second stored procedure then calls a UDF passing in two variables to be multiplied ans this should set the OUTPUT variable to the result.
The UDF does return the correct result to the interior stored procedure, but the interior stored procedure does not pass the value back to the original stored procudure resultint in a Result value = NULL.
The code is below, just copy / past, execute and you will see exactly what I mean.
Any thoughts?
USE NORTHWIND
GO
CREATE FUNCTION RECTANGULAR_XSECTION
(@fWidth float, @fHeight float)
RETURNS float
AS
BEGIN
RETURN (@fWidth * @fHeight)
END
GO
CREATE PROCEDURE usp_shapes_GetRectangularXSection
@fResult float OUTPUT
AS
declare @fWidth float, @fHeight float
SELECT @fWidth = 108, @fHeight = 10
SELECT @fResult = [dbo].[RECTANGULAR_XSECTION](@fWidth, @fHeight)
SELECT @fResult AS CalledProcedureOkHere
GO
CREATE PROCEDURE usp_shapes_GetXSection
@fResult float OUTPUT
AS
EXECUTE usp_shapes_GetRectangularXSection @fResult
SELECT @fResult AS CallingProcedure_NULL?
GO
declare @fResult float
EXECUTE usp_shapes_GetXSection @fResult
SELECT @fResult as OutsideCallingProcedure_NULL?
GO
DROP FUNCTION RECTANGULAR_XSECTION
GO
DROP PROCEDURE usp_shapes_GetRectangularXSection
GO
DROP PROCEDURE usp_shapes_GetXSection
GO
Mike B
I am trying to improve my SQL and reduce the number of connections my website is making to the server.
Currently I have a stored procedure that gets all the games (and their details) for a single user which works fine which uses a WHERE userid = @userid which is an Int value.
I now need to create a new procedure which brings back the to all their "friends" (again by their userid). How is it best to do this? I originally tried to do a WHERE userID IN (@userid) but was unable to work out what variable type to use for @userid.
Is it best to do a single query in this way or is there a way to use the existing SP, loop through the collection of userids and join the result together into a single set to return? If an IN is the best route, what is the correct datatype for the variable?
can anyone help me out.... i need to compare a date that was retrieved from the database and the system date then it must be coded in the stored procs of the database.. help!!!!
View 10 Replies View RelatedHi,
I have a search returning many 'car' records (regno, mileage, color, etc)
In a seperate table I have two columns: accessory and regno. This can have many rows for each regnumber.
Within the storedprocedure that returns the 'car' records I need to also return the relevant accessories for that car as one column.
I have written a function as such (with some help!):
ALTER Function getAccs(@regNo varChar(20))
RETURNS varChar
AS
BEGIN
Declare @List varchar(1000)
SELECT @List = COALESCE(@List + ', ', '') + accessory
FROM frxrep2.dbo.usedaccessories WHERE regnumber = @regno
return @List
END
I was hoping that I could simply use this in the 'car' SELECT statement.
ie:
SELECT regNo, color, mileage, dob.getAccs(regno) as AccessoryList
FROM tableBla etc
I'm not even sure if the function works - How can I test in SQL analyzer?
any help much appreciated,
Pete
Hi,
I know that we cannot have User Defined Functions in SQL 7.0, but is there a work around. I am trying to standardize the date according to the time zone using a function and use it as part of a select statement.
I cannot believe that is not possible in SQL 7.0, can somebody tell me what is the work around, I am running against time. I really appreciate any help on this one.
Satish.
Here is my problem - I have a user defined function that needs the FIRST value from a specially sorted result set. When I do my set statement I get the LAST value in my result set. I can't figure out how in a SQL function to only get that first value (since I can't do a cursor or temp table).
Example: My table has a keyid (created by identity value, the value I need)
Also it has a vendorid, a repid, and an inventoryid. These tie out to other tables.
If I sort my result set in a certain descending order, the first record's keyid is the value I need. Problem is that I am getting the last keyid instead of the first. I can't switch my sort though and get the right value, because of some well-placed zeros. Hard to explain.
I guess what I want is something like:
Select @keyid = FIRST keyid from sortedtable order by vendorid desc, inventoryid desc, repid asc
It's the select FIRST that hangs me up. Can't find that function - does it or something like it exist that I can use from within my function? I'm sure it can be done, I'm just sure I don't know how... Thanks for your help. - K
Hi everyone
I am implementing an encrypted data system whereby captured data is encrypted using MS CAPICOM when written to DB and decrypted when displayed. It works well. However, each time you write the data back on update the encryption is different from the previous time.
I have a search screen that allows users to enter text data and submit it looking for matches. Of course, the user is entering unencrypted text and the DB data is encrypted.
This means that you can't encrypt the input data before you try to match because the encryption alogorithm used on the input data will not match that which was used to encrypt the DB data.
Are you with me so far?
So, you have to compare the uncencrypted input data with DECRYPTED DB data - in other words, decrypt the DB data on the fly within the where clause of your query.
I have accomplished this by writing a UDF that instantiates an instance of the CAPICOM encryption module and calling the UDF when applying the query
eg where udf(columnname1) = 'inputtext' or udf(columnname1) = 'inputtext'.
It works, I get the results that I want.
But, alas, the performance has taken a search severe hit.
Has anyone else ventured down this road?
Is there a better way of doing this?
Thanks
Ray
hi all,
I made myself a user defined function, it works great, but is there a way to make it available to all databases?
cheers,
alex
When I define a UDF in SQL 2000 I can't execute it without qualifiction. Here's the code:
CREATE FUNCTION GetLastShipDate(@DO_No CHAR(10))
RETURNS CHAR(10)
-- DROP FUNCTION GetLastShipDate
BEGIN
DECLARE @v_last_ship_date CHAR(10)
SELECT @v_last_ship_date = COALESCE(CONVERT(CHAR(10), MAX(Asn.Asn_Ship_DateTime), 110), '')
FROM Asn,Asn_Do
WHERE Asn_Do.Asn_Number=Asn.Asn_Number
AND Asn_Do.DO_Number = @DO_No
AND Asn.ASN_STATUS in ('SENT','RESENT')
RETURN @v_last_ship_date
END
So I have to execute this with:
SELECT dbo.GetLastShipDate('T010215004')
I want to execute it with
SELECT GetLastShipDate('T010215004')
What am I doing wrong?
Hi,
Iam new to sql server. I want to create a user defined function to get date in a year(starting day of a week-i.e sunday date) when we give week number as argument. can any body help on this issue?
Is there a way to call a SQL user-defined function in a DTS, perhapsin an ActiveX transformation? I want the destination table to have avalue that was modified by a pre-defined function.-Noelle
View 1 Replies View Related