Stored Proc Help Needed

Aug 8, 2007

 I have a created a stored procedure which gets the values from the 'Orders' table. The procedure goes like this:


CREATE PROC DataSource_GetDetails@OrderId int

ASDECLARE @ColNumber int

SELECT @ColNumber = (Select Coalesce(Col_length('Orders','BranchId'),0))

IF(@ColNumber >0)

BEGIN

SELECT Orders.OrderID,

Orders.Buyer,Orders.Seller

Orders.BranchIDFROM Orders

WHERE Orders.OrderID = @OrderId

END

ELSE

BEGIN

SELECT Orders.OrderID,

Orders.Buyer,Orders.Seller

FROM Orders

WHERE Orders.OrderID = @OrderId

END
 Here  i check whether a column named 'BranchId' exists in the 'Orders' table in a particular database and based on the result 2 different select statements are returned. The problem is, if i run this stored proc in the database which does not have a column 'BranchId' it throws me an errorMsg 207, Level 16, State 3, Line 1

Invalid column name 'BranchID'.
How do i tackle this in the stored proc?
Thanks

View 22 Replies


ADVERTISEMENT

Trigger/Stored Proc Help Needed

Nov 29, 2007

I am fairly new to using triggers and stored procs in SQL Server and could use some help.

Scenario:

I have a table on SQL Server database running on a Windows server (of course) that contains information about articles. A second server in the shop is set up as a LAMP (Linux, Apache, MySQL, PHP) box. What I would like to do is any time the SQL server table is updated or a record is added, tables in MySQL would then be updated to reflect the changes on the first server. My thought is to try and do this with triggers and a stored proc.

Questions:
1) Assuming MySQL can be accessed through ODBC, can an ODBC connection be set up inside a stored proc or trigger, or would this have to be done through the Windows ODBC Data Sources tool in the control panel?

2) Can a Stored Proc be used in a trigger?

3) Can a Stored Proc call an outside function, such as an API call for third party software?

Thanks in advance.

View 1 Replies View Related

Can You Trace Into A Stored Proc? Also Does RAISERROR Terminate The Stored Proc Execution.

Feb 13, 2008

I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code.
So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message:
Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages.
(1 row(s) affected)
(1 row(s) affected)
I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution?
Also, Is there a way to trace into a stored procedure through Query Analyzer?
-------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised:
SELECT  @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND  Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId
IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END 
 

View 3 Replies View Related

Where_Am_Istored Proc Needed!

May 20, 2004

I have seen in the past this stored proc someone posted to find out any string value from anywhere in a database like,

exec where_am_i 'Joe%' will give me all the stored procs or tables where 'Joe..' appears.
Any one has this stored proc?

Please advise!

View 2 Replies View Related

Stored Proc - Calling A Remote Stored Proc

Aug 24, 2006

I am having trouble executing a stored procedure on a remote server. On my
local server, I have a linked server setup as follows:
Server1.abcd.myserver.comSQLServer2005,1563

This works fine on my local server:

Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName

This does not work (Attempting to execute a remote stored proc named 'Data_Add':

Exec [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.Data_Add 1,'Hello Moto'

When I attempt to run the above, I get the following error:
Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'.
No entry found with that name. Make sure that the name is entered correctly.

Could anyone shed some light on what I need to do to get this to work?

Thanks - Amos.

View 3 Replies View Related

Stored Proc Question : Why If Exisits...Drop...Create Proc?

Jun 15, 2006

Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK

View 3 Replies View Related

ASP Cannot Run Stored Proc Until The Web User Has Run The Proc In Query Analyzer

Feb 23, 2007

I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.

View 1 Replies View Related

Calling A Stored Proc From Within Another Stored Proc

Feb 20, 2003

I have seen this done by viewing code done by a SQL expert and would like to learn this myself. Does anyone have any examples that might help.

I guess I should state my question to the forum !

Is there a way to call a stored proc from within another stored proc?

Thanks In Advance.

Tony

View 1 Replies View Related

Stored Proc Calls Another Stored Proc

Jan 13, 2006

Hi all,

I have a stored procedure "uspX" that calls another stored procedure "uspY" and I need to retrieve the return value from uspY and use it within uspX. Does anyone know the syntax for this?

Thanks for your help!
Cat

View 5 Replies View Related

Calling Stored Proc B From Stored Proc A

Jan 20, 2004

Hi all

I have about 5 stored procedures that, among other things, execute exactly the same SELECT statement

Instead of copying the SELECT statement 5 times, I'd like each stored proc to call a single stored proc that executes the SELECT statement and returns the resultset to the calling stored proc

The SELECT statement in question retrieves a single row from a table containing 10 columns.

Is there a way for a stored proc to call another stored proc and gain access to the resultset of the called stored proc?

I know about stored proc return values and about output parameters, but I think I am looking for something different.

Thanks

View 14 Replies View Related

Calling T SQL Stored Proc From CLR Stored Proc

Aug 30, 2007

I would like to know if the following is possible/permissible:

myCLRstoredproc (or some C# stored proc)
{
//call some T SQL stored procedure spSQL and get the result set here to work with

INSERT INTO #tmpCLR EXECUTE spSQL
}

spSQL
(

INSERT INTO #tmpABC EXECUTE spSQL2
)


spSQL2
(
// some other t-sql stored proc
)


Can we do that? I know that doing this in SQL server would throw (nested EXECUTE not allowed). I dont want to go re-writing the spSQL in C# again, I just want to get whatever spSQL returns and then work with the result set to do row-level computations, thereby avoiding to use cursors in spSQL.

View 2 Replies View Related

Execute Stored Procedure Y Asynchronously From Stored Proc X Using SQL Server 2000

Oct 14, 2007

I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.

View 3 Replies View Related

How Can I Call One Or More Stored Procedures Into Perticular One Stored Proc ?

Apr 23, 2008

Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?



How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.

View 1 Replies View Related

Calling A Stored Procedure From Within A Stored Proc

Dec 18, 2007

Hi Peeps
I have a SP that returns xml
I have writen another stored proc in which I want to do something like this:Select FieldOne, FieldTwo, ( exec sp_that_returns_xml ( @a, @b) ), FieldThree from TableName
But it seems that I cant call the proc from within a select.
I have also tried
declare @v xml
set @v = exec sp_that_returns_xml ( @a, @b)
But this again doesn't work
I have tried changing the statements syntax i.e. brackets and no brackets etc...,
The only way Ive got it to work is to create a temp table, insert the result from the xml proc into it and then set @v as a select from the temp table -
Which to be frank is god awful way to do it.
 Any and all help appreciated.
Kal

View 3 Replies View Related

Under Which Filegroup Are Stored Proc. Stored?

Aug 23, 2007

When you create a Stored procedure, is it automatically stored under the default Filegoup?

How can I see under which Filegroup my Stored Procedures and Triggers are stored?

View 2 Replies View Related

FoxPro Triggers Call FoxPro Stored Proc Calls SQL Server Stored Procedure

Mar 10, 2005

I didn't want to maintain similar/identical tables in a legacy FoxPro system and another system with SQL Server back end. Both systems are active, but some tables are shared.

Initially I was going to use a Linked Server to the FoxPro to pull the FP data when needed. This works. But, I've come up with what I believe is a better solution. Keep in mind that these tables are largely static - occassional changes, edits.

I will do a 1 time DTS from FP into SQL Server tables.

I then create INSERT and UPDATE triggers within FoxPro.

These triggers fire a stored procedure in FoxPro that establishes a connection to the SQL Server and fire the appropriate stored procedure on SQL Server to CREATE and/or UPDATE the corresponding table there.

In the end - the tables are local to both apps.

If the UPDATES or TRIGGERS fail I write to an error log - and in that rare case - I can manually fix. I could set it up to email me from within FoxPro as well if needed.

Here's the FoxPro and SQL Server code for reference for the Record Insert:

FOXPRO employee.dbf InsertTrigger:
employee_insert_trigger(VAL(Employee.ep_pk),Employ ee.fname,Employee.lname,Employee.email,Employee.us er_login,Employee.phone)

FOXPRO corresponding Stored Procedure:
FUNCTION EMPLOYEE_INSERT_TRIGGER
PARAMETERS wepk,wefname,welname,weemail,WEUSERID,WEPHONE

nhandle=SQLCONNECT('SS_PDITHP3','userid','password ')

IF nhandle<0
m.errclose=.f.
IF !USED("errorlog")
USE tisdata!errorlog IN SELECT(1)
m.errclose=.t.
ENDIF

SELECT errorlog
INSERT INTO errorlog (date, time, program,source,user) ;
values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nhandle<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username"))

IF m.errclose
USE IN errorlog
ENDIF
RETURN

ENDIF
nquery="exec ewo_sp_insertNewEmployee @WEPK ="+STR(wepk)+",@WEFNAME ='"+wefname+"',@WELNAME ='"+welname+"',@WEEMAIL ='"+weemail+"',@WEUSERID ='"+weuserid+"',@WEPHONE='"+wephone+"',@RETCODE =0"
nsucc=SQLEXEC(nhandle,nquery)

SQLDISCONNECT(nhandle)

IF nSucc<0
m.errclose=.f.
IF !USED("errorlog")
USE tisdata!errorlog IN SELECT(1)
m.errclose=.t.
ENDIF

SELECT errorlog
INSERT INTO errorlog (date, time, program,source,user) ;
values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nSucc<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username"))

IF m.errclose
USE IN errorlog
ENDIF
ENDIF

RETURN

SQL SERVER Stored Procedure called from FOXPRO Stored Procedure
CREATE procedure ewo_sp_insertNewEmployee (
@WEPK int,
@WEFNAME char(20),
@WELNAME char(20),
@WEEMAIL char(50),
@WEUSERID char(15),
@WEPHONE char(25),
@RETCODE int OUTPUT
)

AS

insert into WO_EMP (
WE_PK,
WE_FNAME,
WE_LNAME,
WE_EMAIL,
WE_USERID,
WE_PHONE
)

VALUES (
@WEPK,
@WEFNAME,
@WELNAME,
@WEEMAIL,
@WEUSERID,
@WEPHONE
)


IF @@ERROR <> 0
BEGIN
SET @RETCODE=@@ERROR
END
ELSE
BEGIN
-- SUCCESS!!
SET @RETCODE=0
END

return @RETCODE
GO

View 2 Replies View Related

Stored Procedure Help Needed

Jun 22, 2005

SP's are still not my forte, but getting there!I am trying to hit two tables in this one and return the values but keep getting the error code = 1 built into the sp and can't see why.CREATE PROCEDURE dbo.retrieveScores @studentId         VARCHAR(20), @courseId         VARCHAR(20), @lessonLocation    VARCHAR(20)  OUTPUT, @lessonStatus      VARCHAR(20)  OUTPUT,  @lessonScoreRaw      VARCHAR(10)  OUTPUT, @lessonScoreMin      VARCHAR(10)  OUTPUT, @lessonScoreMax      VARCHAR(10)  OUTPUT,  @objId            VARCHAR(20)  OUTPUT, @objStatus        VARCHAR(20)  OUTPUT, @objScoreRaw      VARCHAR(10)  OUTPUT, @objScoreMin      VARCHAR(10)  OUTPUT, @objScoreMax      VARCHAR(10)  OUTPUT  ASBEGIN DECLARE @errCode   INT
 DECLARE c_courseScores CURSOR FOR  SELECT lessonLocation, lessonStatus,   lessonScoreRaw, lessonScoreMin, lessonScoreMax          FROM   courseScores  WHERE  studentId = @studentId AND courseId = @courseId FOR READ ONLY
 OPEN c_courseScores  FETCH c_courseScores INTO @lessonLocation, @lessonStatus,    @lessonScoreRaw, @lessonScoreMin,         @lessonScoreMax CLOSE c_courseScores DEALLOCATE c_courseScores
 DECLARE c_objScores CURSOR FOR  SELECT  objId, objStatus,    objScoreRaw, objScoreMin, objScoreMax          FROM   objScores  WHERE  studentId = @studentId AND objId = @objId AND courseId = @courseId FOR READ ONLY
 OPEN c_objScores  FETCH c_objScores INTO    @objId, @objStatus, @objScoreRaw, @objScoreMin,         @objScoreMax CLOSE c_objScores DEALLOCATE c_objScores
 
 IF( @@FETCH_STATUS <> 0 ) BEGIN  SET @errCode = 1  GOTO HANDLE_APPERR END
 SET @errCode = 0 RETURN @errCode
HANDLE_APPERR: IF( CURSOR_STATUS( 'local', 'c_courseScores' ) >= 0 ) BEGIN  CLOSE c_courseScores  DEALLOCATE c_courseScores END
 SET @errCode = 1 RETURN @errCodeENDGOSuggestions?Thanks all,Zath

View 1 Replies View Related

Help With Stored Procedures Needed !!!

Sep 24, 1998

Hi,

I am trying to create a SP where selected fields are passed as parameters. Can
this be done ?

Thank you for your time,
Robert

View 2 Replies View Related

Stored Procedure Help Needed...please Help Me.

Sep 30, 2007

userid logintime logouttime subject question answer correct status
1000 9/2/2007 12:10:10 chemistry 0
1000 chemistry 1 T 1
1000 chemistry 2 F 0
1000 chemistry 3 a 1
1000 chemistry 4 a 0
1000 chemistry 5 b 1
1000 9/2/2007 12:20:30 chemistry 1
3000 9/2/2007 12:40:00 Math 0
3000 Math 1 c 1
3000 Math 2 a 1
3000 Math 3 T 0
3000 Math 4 F 1
3000 Math 5 b 0
3000 9/2/2007 12:50:25 Math 1
2000 9/2/2007 12:10:10 Biology 0
2000 Biology 1 T 1
2000 Biology 2 F 0
2000 Biology 3 a 1

Logintime means time person started the test.
Logouttime means time person stopped the test.
Correct 1 means correct answer
Correct 0 means wrong answer
Status 0 means test is started.
Status 1 means test is stopped. (Some time people don't stop test due to network failure. So that record got no status 1 or logouttime).


i need to find total score of correct answer.

Based on below table, i need to find who passed and who failed.
Subject Total_Question Main_Question Pass_Score
Math 5 3,5 3
Biology 5 4 4
Chemistry 5 1,2 5

i need to write a stored procedure to do calculation.....so i want to find the below result......

userid logintime logouttime Duration Subject Score Question_Missed Result
1000 9/2/2007 12:10:10 9/2/2007 12:20:30 10:20 Chemistry 2 2 Fail
3000 9/2/2007 12:40:00 9/2/2007 12:50:25 10:25 Math 3 3,5 Pass
2000 9/2/2007 12:10:10 Biology 2 4 Fail
How can i combine both table and get this result?...Please help me. I need help.







View 13 Replies View Related

Sql And Stored Proc

Jul 19, 2007

Im trying to perform an update with a stored procedure thats all working but
 an exception is thrown ....violation of primary key contraint....cannot insert duplicate pri key
I understand whats going on but how do you update some ones details if its protected this way.
?

View 3 Replies View Related

Help With SQL Stored Proc

Feb 22, 2008

I have a stored procedure , where i want to return identity column after insert but before insert i want to check if the record exist then select its identity value to return , after select statement it is retutrning null  CREATE PROCEDURE SP_Attendance1 (@DIVISIONID int,@EMPLOYEEID int,@CALLDATE datetime,@RECEIVEDDATE datetime,@DOC datetime,@SYSTEMNAME VARCHAR(20),@DELETED int,@attendanceID int output) AS---DECLARE @ID intIF EXISTS (SELECT ID  FROM TBLATTENDANCE WHERE EMPLOYEEID = @EMPLOYEEID AND YEAR ( CALLDATE ) = YEAR ( @CALLDATE)AND MONTH (CALLDATE) = MONTH ( @CALLDATE) AND DAY (CALLDATE) = DAY ( @CALLDATE)  )BEGIN SET  @attendanceID = SCOPE_IDENTITY()END ELSE BEGIN INSERT INTO TBLATTENDANCE  (DIVISIONID ,EMPLOYEEID ,CALLDATE , RECEIVEDDATE ,DOC,SYSTEMNAME ,DELETED )VALUES  (@DIVISIONID ,@EMPLOYEEID ,@CALLDATE , @RECEIVEDDATE ,@DOC,@SYSTEMNAME ,@DELETED ) ;SELECT DIVISIONID, EMPLOYEEID, CALLDATE, RECEIVEDDATE, DOC, SYSTEMNAME, DELETED,ID  FROM TBLATTENDANCE WHERE (ID = SCOPE_IDENTITY())SELECT  @attendanceID = SCOPE_IDENTITY()ENDGO  Kindly help with this  

View 5 Replies View Related

SQL Stored Proc

Jan 15, 2004

Hi,

I am trying to create a stored proc, that delivers a recordset, per the user requirements BUT,

I want to create a Geneirc search Proc that can handle a few criteria

I was wondering if it is possible to create a VB like Select case
depending on Information supplied to the stored proc

i.e
@Loc_Thing
@Loc_OtherThing

SELECT FirstName,LastName,CIty,Job,,Company,Webpage FROM RECORDSET WHERE
Select case @Loc_Thing
Case "Mickey"
LastName = @Loc_OtherThing
Case "Walt"
Company = @Loc_OtherThing

...... etc

is it possible to create a strored proc like this?
I have found a Select case in SQL, but it doesn't work I would like it?
Any Idea's?

View 1 Replies View Related

My First Stored Proc

Nov 29, 2004

Hello,
So I created my first SP today which returned data to populate a datagrid. Worked to perfection. Now I'm trying to do a simple login and I'm having a hard time getting it together. I want the user to enter in a username and pass then if user exists, to return their userid and update their last login date. But I can't get it. Any help would be awesome.

Here's my SP...

CREATE PROCEDURE [dbo].[userLogin] @username varchar(50), @pass varchar(50) AS

declare @x int
declare @userid int


if exists (SELECT userid FROM users WHERE username = @username AND password = @pass)
set @x = 1
else
set @x = 0

if @x = 1
UPDATE users SET lastlogin = getdate() WHERE userid = (SELECT userid AS name FROM users WHERE username = @username AND password = @pass)
else
return '0'

if @x = 1
SET @userid = (SELECT userid FROM users WHERE username = @username AND password = @pass)
return @userid
GO


Any constructive criticism on my SP is welcome. Also, should I be using Print or Return if I want to send back a value to my VB code?

And my VB:

Dim user As String = txtUsername.Text
Dim pass As String = txtPassword.Text
Dim objDataSet As DataSet
Dim objAdapter As SqlDataAdapter

Try
Dim cmd As New SqlCommand

'Enter Param's here
Dim myParam1 As SqlParameter = cmd.Parameters.Add("@username", SqlDbType.VarChar)
Dim myParam2 As SqlParameter = cmd.Parameters.Add("@pass", SqlDbType.VarChar)

myParam1.Value = user
myParam2.Value = pass

objAdapter = New SqlDataAdapter
objAdapter.SelectCommand = cmd
objAdapter.SelectCommand.Connection = SqlConn
objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
objAdapter.SelectCommand.CommandText = "userLogin"

SqlConn.Open()

Dim str As String = cmd.Parameters("@userid").Value()
cmd.ExecuteNonQuery()
If str = "0" Then
'Error Login Page
Response.Redirect("login_err.aspx")
Else
Session("userid") = str
Response.Redirect("home.aspx")
End If

SqlConn.Close()

Catch ex As SqlException
SqlConn.Close()
End Try

View 4 Replies View Related

Need Help With Stored Proc

Feb 13, 2006

I have 2 tables, lets say: Table1 and Table2.

Table1 has an ID field that is unique (PK).

Table2 has the same ID field and a date field which are both
unique (PK, FK)

 

I need to write a stored procedure that will query the ID
field from table1 like

Select distinct(id) from Table1

 

Would return:

ID

1

2

3

4

5

 

Now on the first of every month I want a job to run that
would create a new record for EACH ID from the above query.  The new record would be created in Table2
with the ID and a date (that I will determine), resulting in:

 

ID        DATE

1          01/01/06

2          01/01/06

3          01/01/06

4          01/01/06

5          01/01/06

 

 

The following month, after running it again, the table would
look like:

ID        DATE

1          01/01/06

1          02/01/06

2          01/01/06

2          02/01/06

3          01/01/06

3          02/01/06

4          01/01/06

4          02/01/06

5          01/01/06

5          02/01/06



 I have no idea how to “loopâ€? through this record set for
each record in Table1 and in turn insert into Table2.

View 2 Replies View Related

Need Help With A Stored Proc

Mar 30, 2006

I have a current stored proc that creates records based on
certain criteria.  One of the fields I
have is a SmallDateTime field.  To
populate this from my stored proc, I have this code (for this one field).

 SELECT @myLeaveDate = CAST(STR(MONTH(getdate()))+'/'+STR(01)+'/'+STR(YEAR(getdate())) AS DateTime) 

This always creates a record for the 1st of the
current month.  This works fine as is.  After it runs I can look at the table and it
creates dates that look like the following: “2/1/2006� -Notice it doesn’t have
any minutes, seconds, etc.

 

Now what I need is to do something similar in another field
which is also SmallDateTime, BUT I want the date to be for the 10th day
of the following month.


I got this working using dateadd, but it also appends the minutes, seconds,
etc.

View 1 Replies View Related

Stored Proc

May 9, 2001

Can I call a sp from within a sp ?
Is this a fairly normal practice ?

Thanks,
Ivan

View 2 Replies View Related

NEED HELP ON STORED PROC

Sep 15, 1999

Can someone help us with this stored proc.
We need the maxccid -1 to be passed as a value.

Here is the proc.

CREATE PROCEDURE NewBillingInfo
@int_acct_id char (10)
@cc_nickname varchar (20),
@cc_zip varchar (20),
@cc_name varchar (100),
@cc_num varchar (20),
@cc_typ varchar (20),
@cc_month char (2),
@cc_year char (4)
as
set nocount off
declare @maxaddrid int
declare @maxccid smallint
/* BEGIN ENCRYPT DATA*/
exec encrypt @cc_zip output, @cc_zip
exec encrypt @cc_name OUTPUT, @cc_name
exec encrypt @cc_num OUTPUT , @cc_num
exec encrypt @cc_typ OUTPUT, @cc_type
exec encrypt @cc_month OUTPUT, @cc_month
exec encrypt @cc_year OUTPUT, @cc_year
BEGIN TRAN
/* get max credit card id for customer and add 1 */
SELECT @maxccid = Max(int_cc_id) from CusCredit where int_acct_id = @int_acct_id
if @maxcced is null
SELECT @maxccid = -1
SELECT @maxccid = @maxccid + 1
INSERT INTO CusCredit
(int_acct_id, int_cc_id, cc_name, cc_num,
cc_typ, cc_month, cc_year, cc_zip, cc_nickname)
VALUES
(@int_acct_id, @maxccid, @cc_name, @cc_num,
@cc_type, @cc_month, @cc_year, @cc_zip, @cc_nickname)
if @@ROWCOUNT < 1
BEGIN
ROLLBACK TRAN
RETURN 103
end
COMMIT TRAN
RETURN
GO

If someone could take a minute and look at this I would be so grateful.

Thanks Dianne Watson

View 1 Replies View Related

A Stored Proc With Like

Nov 29, 2000

I made a strored proc and I receive my like condition in parameter. The like condition must be in '' like (... like 'a%'). I'm sure that it's just a question of apostrophe.

for example:

declare @Command varchar(8000)
declare @Compagnie varchar(100)

select @Compagnie = 'Tonna%'

select @Command = 'SELECT distinct [Site Status].DISTRIBUTION, [Site Status].CC FROM [Site Status] WHERE [Site Status].CC like ' +@Compagnie
execute (@Command)

how can i write that @Compagnie is in apostrophe

thanks

View 1 Replies View Related

Stored Proc Std. Doc?

Feb 16, 2004

Hi,
Anybody has Document for Stored procedure coding standard for best performance.
I know that I can able get in BOL and sql-server-performance.com just wanted know if anybody had info other than this.
Thanks,
Ravi

View 1 Replies View Related

T-sql Stored Proc

Aug 28, 2002

I want to generate getdate without the timestamp, only the date. Any ideas. Datepart will not convert to varchar as needed within this proc.

View 2 Replies View Related

Stored Proc / T-SQL

Jun 20, 2001

One of the columns I am inserting into may or may not contain sensitive data. If the "type" parameter supplied with the proc. execute statement is of a specific value I need to utilize an encryption function (and a data type conversion) on the following column which is the actual value parameter. If the type does not meet the specific criteria the encryption/conversion for the value parameter.
Any syntax assist would be greatly apprciated.

View 2 Replies View Related

Using A Stored Proc Within A Stored Proc

Jul 17, 2004

I want to determin if a user belongs to a certain group or not within a stored proc:


CREATE PROCEDURE usp_General_IsEstimator
@cLogin sysname,
@bEstimator bit

AS

DECLARE groups CURSOR FOR EXEC sp_helpuser @cLogin


What I was planning on doing is to see if the user login returns the 'Estimating' group name using the sp_helpuser. If so, set @bEstimator = 1 else 0

How can I accomplish this?

I cannot use the cursor with the stored proc. How do I work with the recordset returned by the stored proc?

Any ideas?

Mike B

View 2 Replies View Related

Help Stored Proc

Oct 31, 2004

Hi,
Could any one please help me in creating sp.
It should accept one input parameter. when you pass value 1 to this parameter it should show all odd numbers from 1 to 100 and when you pass value 2 it should show all even numbers from 1 to 100.

Thanks in advance.
-ss

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved