Function Inside The Procedure
Jan 29, 2008
i have created the table empid,empname,dob,salary for the insert i need to create procedure of insert empid,empname,dob,salary i got the function for autogenerated number for empid
mmy question how to put function inside the procedure?
my function below,pls guide me its very urgent.
create function NextCustomerNumber()
returns char(5)
as
begin
declare @lastval char(5)
set @lastval = (select max(customerNumber) from Customers)
if @lastval is null set @lastval = 'C0001'
declare @i int
set @i = right(@lastval,4) + 1
return 'C' + right('000' + convert(varchar(10),@i),4)
end
Desikankannan
View 1 Replies
ADVERTISEMENT
May 15, 2008
I have several UDFs created. Inside one of the UDFs I need to execute a dynamic SQL statement and then take that result and do something else with it, before returning the final value.
I know you can not execute a stored proce from inside a function. I also know you can not use the EXEC statement.
I did read that you could use an external stored procedure and/or managed CLR procedures inside a function.
I have created a managed procedure CLR (C#) that simply executes a passed statemetn and returns the value to the calling routine. I have this all coded and is working fine.
However, I am struggling with knowing how to call this CLR procedure from inside my function, seeing how I can not use EXEC statement.
Any advice on how to do this?
Thanks,
Bruce
View 1 Replies
View Related
May 26, 2008
Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it?
What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results.
Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application.
However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password.
Looking forward for replies from expert here. Thanks in advance.
Note: Hope my explaination here clearly describe my current problems.
View 4 Replies
View Related
Aug 23, 1999
Can I write a function inside a Select statement in sql server 7.0
If so HOW ?
Manish Mehta
View 2 Replies
View Related
Apr 10, 2007
Is it possible to write dynamic sql on scalar function and assign the value to return value? I like some thing like below but it is not working...
Thanks
______________________________________________________________________
set @sql = 'select @CallLegKey= min(calllegkey) as CallLegKey
from rt_'+@platform+'_CallLegs
where datediff(day,convert(datetime, CallEndTime) ,'''+cast(@today as varchar(20))+''') = '+cast(@cutoff as varchar(5))
exec @sql
return @CallLegKey
View 3 Replies
View Related
May 9, 2007
Please find my second post in this thread.
Can anyone help me in sorting out the problem and let me know what might be the reason.
Thanks & Regards
Pradeep M V
View 19 Replies
View Related
May 28, 2008
Hello,
when trying to execute the following query with SQL CE 3.1 and OLEDB on WM2003:
SELECT C.Panel_Id, C.Panel_Tier, C.Panel_Type, C.Panel_No, C.Panel_Position
FROM tblMeasurements AS A, tblAssignment_Ant_Pan AS B, tblPanels AS C
WHERE (A.Measurement_No=?) AND (A.Antenna_No = B.Antenna_No) AND (B.Panel_Id = C.Panel_Id) AND C.Panel_Position in
(SELECT Panel_Position FROM tblMeasurement_Results
WHERE (Measurement_No=?) AND ABS(Measurement_Value) BETWEEN ? AND ?
GROUP BY Panel_Position)
i get this error returned:
0x80040E1DL -- DB_E_UNSUPPORTEDCONVERSION -- Requested conversion is not supported.
I don't know where inside the sql string a conversion is necessary/fails.
Surprisingsly when i modify the sql statement a little, it is executed WITHOUT ERRORS:
SELECT C.Panel_Id, C.Panel_Tier, C.Panel_Type, C.Panel_No, C.Panel_Position
FROM tblMeasurements AS A, tblAssignment_Ant_Pan AS B, tblPanels AS C
WHERE (A.Measurement_No=?) AND (A.Antenna_No = B.Antenna_No) AND (B.Panel_Id = C.Panel_Id) AND C.Panel_Position in
(SELECT Panel_Position FROM tblMeasurement_Results
WHERE (Measurement_No=?) AND Measurement_Value BETWEEN ? AND ?
GROUP BY Panel_Position)
The only difference between the 2 statements is the ABS() function inside the sub query.
More surprisingly, with the query analyser on the PDA i can execute both statements fine. I am absolutely confused now where i have to search for the mistake.
I would appreciate it very much if someone out there knows an answer or a hint and could tell me.
With kind regards,
Andre
View 5 Replies
View Related
Jul 20, 2005
Hi!I have a scalar function that returns integer:xview (int)Now, I'm trying to build a procedure that has the following selectinside:select atr1, xview(atr2)from tablenameBut, I get the 'Invalid name' error when I try to execute thatprocedure.If I got it right, I must use user.fn_name() syntax, but I cannot usedbo.xview() inside my procedure since it means xview will always beexecuted as dbo, which is unaccaptable.I'm a bit confused, so any hint is very welcomed.Thanks!Mario.
View 4 Replies
View Related
Apr 8, 2008
help with solution for use order BY inside function and VIEW on the sql server
i explain the problem
i am a web developer in asp page
i BUY an DATA grid object for my web site
the problem in this object i can use only ONE field to use ORDER BY
can i store in the sql server function and VIEW that change the ORDER of the result inside the sql server ?
so whan i do
SELECT *
FROM dbo.tb_pivot_big
the sql server ORDER for ME the result and run this (inside the sql server )
Code Snippet
SELECT *
FROM dbo.tb_pivot_big
ORDER BY new_unit, Fname ASC, val_orginal desc
TNX for help
View 14 Replies
View Related
Aug 17, 2007
I have created an assembly with permission set safe, and a function inside the assembly.
The function reads data from the same SQL Server as it is running inside. For connection I use the "Context connection = true", and the function has the SystemDataAccessKind attribute set to Read.
However when I execute my CLR function I get an error saying something like:
"The request for permission of type System.Data.SqlClient.SqlClientPermission...... failed"
I do, as the login user, have been granted the necessary rights, so I don't believe this is the answer to the error.
And my .dll is also signed.
Has this something to do with writing something a config file?
I have had simular problems with reporting services but fixed them by entering a node in the rspolicy.config file. If this is the case here - which .config file should i modify...machine.config?
View 6 Replies
View Related
Feb 12, 2008
Dear all, I have the Following Query:SELECT DATEDIFF(day, GETDATE(), RECENT_RESERVATION)AS Expr1,RECENT_RESERVATION FROM EMP WHERE SUN=empName;when i run it inside the query analyzer, it returns two columns. but if run it inside The ASPX page it retuns only one column wich is RECENT_RESERVATION date.Note: i am using one methode that takes care of reading from SQL and assigning the result into an array, it works fine everywhere, but with this query it dosen't work. Any suggestions??
View 6 Replies
View Related
Feb 13, 2004
Hi, have configured an ODBC linked server for an Adaptive Server Anywhere (ASA6.0) database.
I have to write a function (not a procedure) that receives a number (@Code) and returns 1 if it was found on a table in the linked server, or 0 if not. Looks very simple...
One problem, is that the queries on a linked-server must be made through the OPENQUERY statement, which doesen't support dynamic parameters. I've solved this making the whole query a string, and executing it, something like this:
SET @SQL='SELECT * FROM OPENQUERY(CAT_ASA, ''SELECT code FROM countries WHERE code=' + @Code + ''')'
EXEC sp_executesql @SQL
(CAT_ASA is the linked-server's name)
Then, i would use @@ROWCOUNT to determine if the code exists or not. But before this, a problem appears: sp_executesql is not allowed within a function (only extended procedures are allowed).
Does somebody know how to make what i want?? I prefer to avoid using temporary tables.
Thanks!
View 3 Replies
View Related
Jul 16, 2015
I am need to create comma separated list in sql and I am using below query.
declare @ConcatenatedString NVARCHAR(MAX)
select @ConcatenatedString = COALESCE(@ConcatenatedString + ', ', '') + CAST(rslt.Number AS NVARCHAR)
from
(
select 1 Number
union
select 2 Number
union
select 3 Number
)rslt
select @ConcatenatedString
When I use the above code inside a function, i am not getting desired output.
create function GetConcatenatedValue
AS
(
declare @ConcatenatedString NVARCHAR(MAX)
select @ConcatenatedString = COALESCE(@ConcatenatedString + ', ', '') + CAST(rslt.Number AS NVARCHAR)
from
(
select 1 Number
union
select 2 Number
union
select 3 Number
)rslt
return @ConcatenatedString
)
View 3 Replies
View Related
Mar 29, 2005
Can i get a login name of the user that execute the store procedure that have open the connection to the DB?
thanks, Paolo giusti
View 1 Replies
View Related
Mar 20, 2007
hi all,
i'm wondering if i can use one stored procedure in too cases, this is the senario:
i have stored procedure for admin and another one for user, they have the same select everything is the same except that in admin SP i have where @admin = admin and for user i have where @user = user
if there a way to use if and else to make this happen
this is what i did so far:
CREATE PROCEDURE [test] @admin INT, @user INT, @indexType INT as
if @indexType = 1
begin
SELECT * FROM table WHERE something IN (SELECT * FROM anothertable where admin = @admin)
end
else
begin
SELECT * FROM table WHERE user = @user
end
GO
any suggestion will be very helpful
thanks
View 2 Replies
View Related
Mar 12, 2008
Hi,
I'm creating a generic procedure in the master db and I'm getting the database name as a input parameter.
How do I set the database inside the procedure , according to this parameter?
T-Sql does not accept "USE" inside procedures .
I tried "sp_defaultdb" but when I select from a specific table in the procedure it still does not identify the table as belonging to that database, it still looks for the table in the master db.
View 3 Replies
View Related
Mar 17, 2008
Hi,
we have the senario we want to call oracle table inside the sql stored procedure, it is possible to call oracle table inside the sql stored procedures.
Regards
Kannan.D
Desikankannan
View 3 Replies
View Related
Jul 20, 2005
Hello,I need to have a stored procedure, which performs sorting. Something likethis:CREATE PROCEDURE procname@sortby varchar(30)ASBEGINSELECT some, columnsFROM some_tableORDER BY @sortbyEND(of course, i know this won't work, but it gives the idea of what i mean)Is there a possibility to write a procedure which behaves like that? It isimportant for me not to have multiple procedures just for different sortingcriteria...Thanks,Mike
View 3 Replies
View Related
Aug 5, 2006
I want to execute some insert statements within a stored procedure and commit those changes regardless of any raiseerror that occurs later in the stored procedure. My difficulty is that I am forced to use raiseerror with severity 16 in order to send a message through the powerbuilder application interface (compiled vendor code). I have tried save points but can't get that to save my insert and still present an error to the user about something else that happens later.
here is an example
BEGIN procedure
Insert something and save it even if error is raised below
RaiseError('you made a mistake and need to do this.',16,-1)
END procedure
View 5 Replies
View Related
Mar 31, 2008
Hi folks
I nead to call a stored procedure in a where statemene, but MSSQL dont like that.
My problem is that the StoredProcedure is calling itself recursive and therfore its impossible to add the code as a standard SELECT statement. Here is the code
ALTER PROCEDURE dbo.advsp_FilterRecordRights
@RequesterGUID Char(20),
@EntityGUID Char(20)
AS
BEGIN
if not Exists(Select *
from GUIDRightsH
where GUIDRightsH.EntityGUID = @EntityGUID and
GUIDRightsH.RequesterGUID = @RequesterGUID and
GUIDRightsH.RecProp <> 0)
Begin
Return 1
end
if not Exists(Select *
from UsergroupMembers
where UserGroupMembers.UsergroupGUID = @RequesterGUID and
dbo.advsp_FilterRecordRights(UserGroupMembers.UserGUID,@EntityGUID) = 1)
Begin
Return 1
end
Return 0
END
View 3 Replies
View Related
Aug 14, 2006
Basically I have two strings. Both strings will contain similar data because the 2nd string is the first string after an update of the first string takes place. Both strings are returned in my Stored Procedure
For example:String1 = "Here is some data. lets type some more data"String2 = "Here's some data. Lets type some data here"I would want to change string2 (inside my Stored Procedure) to show the changed/added text highlighted and the deleted text with a strike though.
So I would want string2 to look like thisstring2 = "Here<font color = "#00FF00">'s</font> <strike>is</strike> some data. <font color = "#00FF00">L</font>ets type some <strike>more</strike> data <font color = "#00FF00">here</font>"
Is there an way to accomplish this inside a stored procedure?
View 2 Replies
View Related
May 18, 2007
This example is working:
Declare @startRowIndex INT; set @startRowIndex = (@PagerIndex * @ShowMembers); With BattleMembers as ( SELECT TOP 20 ROW_NUMBER() OVER (ORDER BY LastActivityDate DESC) AS Row, UserId, UserName FROM aspnet_Users) SELECT UserId, UserName FROM BattleMembers WHERE Row between @startRowIndex and @startRowIndex+@ShowMembersEND
and this one doesn't work:USE [xx]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[battle_Paging]@PagerIndex INT,@ShowMembers INT ASBEGINDeclare @startRowIndex INT; set @startRowIndex = (@PagerIndex * @ShowMembers); With BattleMembers as ( SELECT TOP 20 ROW_NUMBER() OVER (ORDER BY aspnet_Users.LastActivityDate DESC) AS Row, aspnet_Users.UserId, aspnet_Users.UserName, mb_personal.Avatar FROM aspnet_Users LEFT JOIN mb_personal ON (mb_personal.UserID=aspnet_Users.UserId) SELECT UserId, UserName, Avatar FROM BattleMembers WHERE Row between @startRowIndex and @startRowIndex+@ShowMembersEND
Error:Msg 156, Level 15, State 1, Procedure battle_Paging, Line 18Incorrect syntax near the keyword 'SELECT'.
I try to join in the table mb_personal here. Some help would be very appreciated! Thanks.
View 2 Replies
View Related
Oct 26, 2007
Hello, I have a situation that I query a table and return multiple rows (email addresses). I want to iterate through the rows and concatenate all email addresses into one string (will be passing this to another stored procedure to send mail). How can I process result rows inside a stored procedure? This is what I have so far: CREATE PROCEDURE [dbo].[lm_emailComment_OnInsert] @serviceDetailID int,@comment varchar(500),@commentDate DateTime,@commentAuthor varchar(100)ASBEGINDECLARE @serviceID intDECLARE @p_recipients varchar(8000)DECLARE @p_message varchar(8000)DECLARE @p_subject varchar(100)/* Grab the Service_id from underlying Service_Detail_id*/SELECT @serviceID = Service_id FROM lm_Service_Detail WHERE Service_Detail_id = @serviceDetailID/* Get email addresses of Service Responsible Parties */
SELECT DISTINCT dbo.lm_Responsible_Party.EmailFROM dbo.lm_Service_Detail INNER JOIN
dbo.lm_Service_Filing_Type ON dbo.lm_Service_Detail.Service_id = dbo.lm_Service_Filing_Type.Service_id INNER JOIN
dbo.lm_Responsible_Party_Filing_Type ON dbo.lm_Service_Filing_Type.Filing_Type_id = dbo.lm_Responsible_Party_Filing_Type.Filing_Type_id INNER JOIN
dbo.lm_Responsible_Party ON dbo.lm_Responsible_Party_Filing_Type.Party_id = dbo.lm_Responsible_Party.Party_idWHERE (dbo.lm_Service_Detail.Service_Detail_id = @serviceDetailID)/* Build message */
SET @p_subject = "KLM - Service ID: " + CAST(@serviceID AS varchar(4))SET @p_recipients = "" /*need string of addresses*/
SET @p_message = @p_message + "Service Detail ID: " + CAST(@serviceDetailID AS varchar(4)) + char(13)SET @p_message = @p_message + "Comment Date: " + CAST(@commentDate As varchar(25)) + char(13)SET @p_message = @p_message + "Comment Author: " + @commentAuthor + char(13)SET @p_message = @p_message + "Comment: " + @comment + char(13)PRINT "subject: " + @p_subject + char(13)PRINT "recip: " + @p_recipients + char(13)PRINT "msg: " + @p_message + char(13)/*Send the email*/
Execute master..xp_sendmail @recipients = @p_recipients, @message = @p_message, @subject = @p_subject END
GO
View 7 Replies
View Related
Feb 28, 2005
I am having problem with 'TOP @pageSize'. It doesn't work, but if I replace it by 'TOP 5' or 'TOP 6' etc., then the stored procedure runs without errors.
Can someone please tell me how I could use @pageSize here so that it dynamically determines the 'n' of 'TOP n' ?
ALTER PROCEDURE dbo.spGetNextPageRecords
(
@pageSize int,
@previousMaxId int
)
AS
/* SET NOCOUNT ON */
SELECT Top @pageSize ProductId, ProductName
FROM Products
WHERE (ProductID > @previousMaxId) order by ProductId
RETURN
View 4 Replies
View Related
May 7, 2005
Hello :) I need to do something like this:
CREATE PROCEDURE SelectCostumers @name varchar(100)
Declare @SQL = "SELECT Id, Name FROM Costumers"
AS
IF (@name IS NULL)
@SQL
ELSE
@SQL += "WHERE Name LIKE @name"
See, what I need is a string variable that I can concatenate with whatever I want depending on the parameter I get.
Thank you
View 2 Replies
View Related
Feb 26, 2004
I am trying to creating a table inside a stored procedure using SQL that works fine in Query Analyzer. However, when I check the syntax I get the following message:
Error 208: Invalid object name '##OPTIONSEX'
I am using the following SQL script:
CREATE PROCEDURE [dbo].[Test2] AS
CREATE TABLE ##OPTIONSEX
(
OPTION_PLAN VARCHAR(50),
TOT_OPTIONS_EXCHANGED FLOAT NULL
)
GO
INSERT ##OPTIONSEX
SELECT
B.COMPONENT,
TOT_OPTIONS_EXCHANGED = SUM(A.UNITS)
FROM TBLEXERCISEOPTIONS A, TBLCOMPONENT B
WHERE B.COMPONENTID = A.COMPONENTID
GROUP BY B.COMPONENT
GO
Any help getting this to run correctly would be appreciated.
View 3 Replies
View Related
Feb 17, 2014
How the procedure will be called inside the trigger,whether first procedure followed by second or parallel it will execute?
CREATE TRIGGER [dbo].[InsertDatFXActualStaging]
ON [dbo].[DatFXActualStaging]--change this table to DatFXActualStaging
for INSERT
AS
BEGIN
SET NOCOUNT ON;
[Code] ....
View 1 Replies
View Related
Jun 23, 2006
Hi Folks!
I have a cursor inside a stored procedure that should identify rows based on certain criteria and update the columns on those records.
While I try to do so, the cursor goes into an infinite loop although I have only 1 record that matches my criteria. I think that the transaction is not commited and the cursor is picking up the same record for update.
Here is my sample record in table :tblMsg
SenderRef <space> MsgStatusId <space> MsgType <space>Groupid
1281341<space> 1 <space>KBC-NON-ETC-MATCHED-BLIM <space>191902
1281341 <space>18 <space>KBC-RCVD-ADM <space>191902<space>
and here is my code.
CREATE PROCEDURE msg_ResolveADMBeforeBlim
AS
DECLARE @blimGroupId AS INT
DECLARE @admSenderRef AS VARCHAR(50)
DECLARE admCursor CURSOR FOR
SELECT senderref
FROMtblMsg
WHERE msgstatusid = 18
AND msgtype = 'KBC-RCVD-ADM'
FOR UPDATE
OPEN admCursor
FETCH NEXT FROM admCursor INTO @admSenderRef
WHILE @@FETCH_STATUS = 0
BEGIN
--FIND CORRESPONDING BLIM RECORD AND GRAB ITS GROUPID TO UPDATE THE ADM
SELECT @blimGroupId = GroupID
FROM tblMsg
WHERE msgType = 'KBC-NON-ETC-MATCHED-BLIM'
AND SenderRef = @admSenderRef
PRINT 'AFTER SELECT INSIDE WHILE: Senderref- >' + @admSenderRef;
--UPDATE THE ADM RECORD WITH THE GROUPID AND MSGSTATUS = 3
BEGIN
UPDATE tblMsg
SET MsgStatusId = 3, GroupId = @blimGroupId
WHERE CURRENT OF admCursor
END
PRINT 'AFTER UPDATE INSIDE WHILE';
END
CLOSE admCursor
DEALLOCATEadmCursor
The update statement was included beteween a BEGIN TRAN and END TRAN. But no joy.
Any ideas why this would happen?
Thanks for your help.
Arun
View 3 Replies
View Related
Jul 20, 2005
Okay, so I have a problem and I would be REALLY grateful for anyassistance anyone can offer because I have found little or no help onthe web anywhere.I want to access and do joins between tables in two different SQL db'son the same server. Heres what Im dealing with.In one database resides all of my security features for our clients,where it decides who can login, etc etc....In another database, I need to cross reference with a few fields in mysecurity db.See the issue Im running into here is that because the way the peoplehave their databases set up for different products, I would normallyhave to put these tables with security features in every database...which is horrible, because every time I do an update I would have todo it in 12 different places. Thats not efficient at all.So I thought if I had one central DB, where all security features arecontrolled from, that would be perfect... now the issue is crossreferencing and doing joins with other tables that ARENT in the samedb....have I lost you yet?I appreciate all of your help!THANKS!!
View 5 Replies
View Related
Dec 21, 2006
Hi for all
i just get the news that the Dec. CTP of SQL Server 2005 & tools is released !
but my dream is to see debugging stored procedure support inside the SSMSE as debugging applications inside Visual Studio 2005
Many many many thanks to Microsoft and for the great efforts on the cool products
Mohamed
View 3 Replies
View Related
Apr 30, 2008
Hi,
I have to write a stored procedure what repair a table.
It have to delete lost rows before make relation to itself (PK column is 'Kw_KeywordID', FK column is 'Kw_ParentID').
I do not found the way to write this procedure to can create statement from parameter of the procedure.
I would like to pass the table name, but I receive error if the query like 'Select ... Form @Table ....'.
If the parameter is in the 'WHERE', nothing problem.
My procedure is:
CREATE PROCEDURE sp_Repair_IS_KW_AbtKz176
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @OrphanRowCount int
select @OrphanRowCount = 1
while(@OrphanRowCount > 0)
begin
DECLARE KWCursor CURSOR FOR
SELECT count(*) AS cRowCount FROM IS_KW_AbtKz176 WHERE Kw_ParentID IS NOT NULL AND Kw_ParentID NOT IN (SELECT Kw_KeywordID FROM IS_KW_AbtKz176);
OPEN KWCursor;
FETCH NEXT FROM KWCursor INTO @OrphanRowCount
CLOSE KWCursor
DEALLOCATE KWCursor
IF (@@FETCH_STATUS = 0) AND (@OrphanRowCount > 0)
BEGIN
exec('DELETE FROM IS_KW_AbtKz176 WHERE Kw_ParentID IS NOT NULL AND Kw_ParentID NOT IN (SELECT Kw_KeywordID FROM IS_KW_AbtKz176)')
END
end
END
GO
How can I run fully parameterized queries from an SP.
I can make it only like exec('DELETE FROM' + @TableName + ...)
Thank you for any idea,
Imre
View 2 Replies
View Related
Feb 23, 2007
I am currently having a problem where my SQL server seems to lock any variables to 1000 characters (ie. varchar(8000) can only hold 1000)I have read in numerous sources it was possible to change that limit so the varchar can truly hold the 8000 characters and not stop at 1000, but there was no info on how to do this.I am looking for a "How to" to put this limit to 8000.Thank you!
View 10 Replies
View Related
Nov 7, 2007
Dear all,i'm facing a problem with my storedprocedure which happened when i ran my web application and reach to the point where my class invoke this storedprocedure,my SP contains a cursor that built his sql according to certain condition, so i put the "SET @cur Cursor For....." inside the if block (definitely i've declared it under AS keyword directly) and this SP is working well inside sql server(I've tested it), BUT when my ASP.net code invoke this SP it gives me the following error : "The Variable @cur does not currently have a cursor allocated to it" repeated as much as there are IF clauses in my SP,Please Help.Regards,
View 1 Replies
View Related
