Stor Proc 101 Please

Jan 26, 2006

How I can get the best tutorial of using SQL Server 2005 Stored Procedured? For example this stor proc,

--------------------------------------------------------------------------------

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

 

CREATE PROCEDURE [dbo].[COMPANY_ADD]

-- Add the parameters for the stored procedure here

@CompanyID INT,

@CompanyName NVARCHAR(100),

@CreatedDT Datetime

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

insert into COMPANY(CompanyID,  CompanyName, CreatedDT, LastModifiedDT)

values (@CompanyID, @CompanyParentID, @CompanyContactID, @CompanyName, @CreatedDT, @CreatedDT)

return @CompanyID

END

--------------------------------------------------------------------------------------------------------------

I want to get tutorial of the meaning line by line?

Thanks 

View 4 Replies


ADVERTISEMENT

Case Expression Stor Proc, Need Some Help

Jul 10, 2006

ALTER PROCEDURE dbo.TEST_TOTALCALLS
    (
        @varDate as varchar (255),
        @StartDate as datetime,
        @EndDate as datetime
    )
AS

SELECT
CASE @varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
WHEN 'Month' Then DATENAME(mm, CALLSTARTTIME)
END,
COUNT(*) as 'Total Calls'
FROM CALLMASTER
WHERE     (COMMERCIALS = '1') AND (CALLSTARTTIME >= @StartDate) AND (CALLENDTIME <= @EndDate)

GROUP BY
CASE @varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
WHEN 'Month' Then DATEPART(mm, CALLSTARTTIME), DATENAME(mm, CALLSTARTTIME)  ' <---this part gave me an error, because of the comma,
END
ORDER BY
CASE @varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
WHEN 'Month' Then DATEPART(mm, CALLSTARTTIME)
END

The month case is giving me an error. I think it has to do with two expressions in one line.
Anyone know how to combine that into 1 expression? or is there away to work around it?
As I would like to display the month as Name, but group and sort by number.
Thx!~

View 10 Replies View Related

Recompile All Stor Proc And Udf And Views

Oct 1, 2003

Hi ,
what would be correct way to recompile
all user objects ?

Developers working by using alter create and rename on udf and stored procedure, dependecicies window in EM does not repesent correct info.

If I want to see up to date all dependedcies of every user defined object in db

Thank you


Alex

View 5 Replies View Related

Schedule Stor Proc In 2005

May 21, 2007

Hello

I KNEW this would cause some problems, but I just can't seem to find how to do it....

Basically I have a stored procedure in 2005 dbase (which updates the data), and I want to schedule it to run daily (at night) HOW CAN I DO THIS????

:mad: Y did they have 2 go and change it??? :eek:

View 3 Replies View Related

How To Determine If Stor Proc Parameter Is Output Or Input

Jul 23, 2005

I know that you can retrieve whether a parameter is for output buy wayof the "isoutparam" field, but is there anything that tells you whethera parameter is input/output?thanks

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

Processing The Resultset Of Another Proc From A Proc

Apr 8, 2004

Is it possible to retrieve the resultset of a stored procedure from another procedure in sql server 2000.
Basically I am calling proc2 from the inside of proc1.
proc2 returns 2 resultsets. I want to process these two resultsets
from within proc1.

If its possible , please provide sample code.

thanks in advance,
Alok.

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

Resultset Of Proc In Another Proc

Oct 4, 2005

Hello,i want to use the result set from a stored proc in another stored proc, forexample:create stored procedure proc1 (@x int) asdeclare @tbl (y1 int, y2 int)insert into @tbl values(@ * @x, @x * @x * @x)select * from @tblGO--create stored procedure proc2 (@x int) asdeclare @tbl (y1 int, y2 int)while @x > 0 begininsert into @tbl select (exec proc1 @x) <-- this is my problemset @x = @x - 1endselect * from @tblGO--I know i could use output parameters. But i want to know if something ispossible with SQL-Server?thanks,Helmut

View 4 Replies View Related

Calling A Proc From A Proc

Jan 10, 2007

I'm having problems calling my second proc (MyProcedure2) from within anexisting proc. MyProcedure2 does not seeem to fire this way. My code isbelow. Help appreciated.Thanks,TrevorALTER procedure dbo.MyProcedure1(@newsletterid int)ASSET NOCOUNT ON-- Return Subscribersdeclare @howmany intset @howmany=isnull((select count(subscriberid) from mySubscribers wherenewsletterid=@newsletterid),0)update Mynewsletters set status=3,howmany=@howmany wherenewsletterid=@newsletteridselect @howmanyexec MyProcedure2*** Sent via Developersdex http://www.developersdex.com ***

View 4 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

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

Proc Problem

Nov 20, 2006

Hi
I have this stored_proc - which works fine
ALTER PROCEDURE proc_Prod_AdvancedSearch  @Title VARCHAR(50) = Null,  @Category VARCHAR(8) = null,  @ReleaseClass VARCHAR(50) = null,  @BuyPrice DECIMAL(8,2) = nullASSET NOCOUNT ONSELECT *FROM ProductWHERE Title = ISNULL(@title, title)AND  Category = ISNULL(@Category, Category)AND  ReleaseClass = ISNULL(@ReleaseClass, ReleaseClass)AND  BuyPrice < ISNULL(@BuyPrice, BuyPrice)Return
except, when i type in something for the @title parameter, unless i type the exact match as title, it returns nothing
i would like it to return results if part of @title is similar to title
ive tried WHERE Title = ISNULL(' %' + @title + '%' , title) but it does the same thing as above
any ideas
cheers
 

View 4 Replies View Related

SQL Store Proc - Need Help

May 4, 2007

I need some help writing my request, I tried both of the following but it does'st seem to work.  Your sugestion would be appreciated.SELECT count(LeadId) FROM dbo.Cl_Leads Where AccntMng=@AccntMng and (Status = 'Won' or Status = 'Lost') and InsertDate BETWEEN @dtStart AND @dtEnd
 I also tried
SELECT count(LeadId) FROM (SELECT * FROM dbo.Cl_Leads WHERE InsertDate BETWEEN @dtStart AND @dtEnd)Where AccntMng=@AccntMng and (Status = 'Won' or Status = 'Lost')  This return and error: Incorrect syntax near the keyword 'Where'.
 

View 6 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

Strore Proc With Like %

Oct 1, 2004

This must be a simple one but look all over to find infos on the uses of like % in a store proc and I did'nt find anything. I know it's something wrong with my "%" and where to put the apostrophes.

Please could you check the following code and let me know what wrong with it.
Thanks

ALTER PROCEDURE dbo.GetSearchMultiColumn
(
@strColumnNamenvarchar (50),
@strSearchTermnvarchar (200)
)

as
if (@strColumnName = 'Camera')
begin
SELECT SystemId,CompanyName,City,State,Country,Camera1,Camera2,Camera3,Camera4 FROM Customers
WHERE Camera1 LIKE '% @strSearchTerm %' or Camera2 LIKE '% @strSearchTerm %' or Camera3 LIKE '% @strSearchTerm %' or Camera4 LIKE '% @strSearchTerm %'
return 0
end

if (@strColumnName = 'ZMotor')
begin
SELECT SystemId,CompanyName,City,State,Country,ZMotor1,ZMotor2,ZMotor3 FROM Customers
WHERE ZMotor1 LIKE '% @strSearchTerm %' or ZMotor2 LIKE '% @strSearchTerm %' or ZMotor3 LIKE '% @strSearchTerm %'
return 0
end

if (@strColumnName = 'Stage')
begin
SELECT SystemId,CompanyName,City,State,Country,Stage1,Stage2,Stage3 FROM Customers
WHERE Stage1 LIKE '% @strSearchTerm %' or Stage2 LIKE '% @strSearchTerm %' or Stage3 LIKE '% @strSearchTerm %'
return 0
end

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

Store Proc - Need Some Help Please

Jan 7, 2005

I am doing an insert and I get the error "Cast from type 'DBNull' to type 'Integer' is not valid." if the contact name already exist.

I dont see my error so if someone could enlight me I would appreciate.
Thanks


"****************STORE PROC***********************
ALTER procedure dbo.Add_Cl_Contact
(
@ContactNamenvarchar(75),
@Departmentnvarchar (50)=null,
@Titlenvarchar(50)=null,
@Phone1char(20)=null,
@Phone2char(20)=null,
@Phone3char(20)=null,
@Ext1char(10)=null,
@Ext2char(10)=null,
@Ext3char(10)=null,
@Faxnvarchar(50)=null,
@Emailnvarchar(50)=null,
@ContactTypeIDint=null,
@Resultinteger OUTPUT

AS

if Exists
(
Select *
From Cl_Contacts
Where ContactName = @ContactName
)
Return 1
Else

insert into Cl_Contacts (
ContactName,
Department,
Title,
Phone1,
Phone2,
Phone3,
Ext1,
Ext2,
Ext3,
Fax,
ContactTypeID,
Email

)
values (
@ContactName,
@Department,
@Title,
@Phone1,
@Phone2,
@Phone3,
@Ext1,
@Ext2,
@Ext3,
@Fax,
@ContactTypeID,
@Email
)
Select @Result = SCOPE_IDENTITY()
return 0
"****************END STORE PROC***********************


'******************************************************************
'******************************************************************
Public Function Add_Cl_Contact(ByVal strContactName As String, _
ByVal strDep As String, ByVal strEmail As String, ByVal strExt1 As String, ByVal strExt2 As String, _
ByVal strExt3 As String, ByVal strFax As String, ByVal strPhone1 As String, ByVal strPhone2 As String, _
ByVal strPhone3 As String, ByVal strTitle As String, ByVal iContactTypeId As Integer, _
ByRef iResult As Integer, ByRef strError As String) As Boolean
'******************************************************************
Dim bSuccess As Boolean = True
Dim connect As New SqlConnection(strConnection)
Dim cmdSelect As New SqlCommand("Add_Cl_Contact", connect)
Dim paramReturnValue As SqlParameter

cmdSelect.CommandType = CommandType.StoredProcedure
'PARAM
cmdSelect.Parameters.Add("@ContactName", strContactName)
cmdSelect.Parameters.Add("@Department", strDep)
cmdSelect.Parameters.Add("@Title", strTitle)
cmdSelect.Parameters.Add("@Phone1", strPhone1)
cmdSelect.Parameters.Add("@Phone2", strPhone2)
cmdSelect.Parameters.Add("@Phone3", strPhone3)
cmdSelect.Parameters.Add("@Ext1", strExt1)
cmdSelect.Parameters.Add("@Ext2", strExt2)
cmdSelect.Parameters.Add("@Ext3", strExt3)
cmdSelect.Parameters.Add("@Fax", strFax)
cmdSelect.Parameters.Add("@Email", strEmail)
cmdSelect.Parameters.Add("@ContactTypeID", iContactTypeId)

paramReturnValue = cmdSelect.Parameters.Add("@Result", SqlDbType.Int)
paramReturnValue.Direction = ParameterDirection.Output

paramReturnValue = cmdSelect.Parameters.Add("ReturnValue", SqlDbType.Int)
paramReturnValue.Direction = ParameterDirection.ReturnValue

Try
connect.Open()
cmdSelect.ExecuteNonQuery()
iResult = cmdSelect.Parameters("@Result").Value
connect.Close()

If cmdSelect.Parameters("ReturnValue").Value = 0 Then
strError = "Contact has been added"
Else
strError = strContactName & " already exist!"
bSuccess = False
End If

Catch ex As Exception
bSuccess = False
strError = ex.Message
Finally
If connect.State = ConnectionState.Open Then
connect.Close()
End If
End Try
Return bSuccess
End Function

View 3 Replies View Related

Need Help With A Store Proc

Jun 29, 2005

For a reason that I dont see my store proc is always
returning 0 records but if I use a commandType.text instead of 
StoredProcedure then I get my results.  So I must have some kind
of error somewhere. 
Store Proc
ALTER PROCEDURE dbo.Get_Cl_IssuesBySystemID

    @SystemId        nvarchar(255)

AS

    SET NOCOUNT ON

SELECT  t1.issueId, t1.IssueTitle,t1.DateCreated,t2.CustomFieldValue

FROM dbo.IssueTracker_Issues t1


LEFT OUTER JOIN dbo.IssueTracker_ProjectCustomFieldValues t2

ON t1.IssueId = t2.IssueId

   

Where t2.CustomFieldId=5 and t2.CustomFieldValue like '%@SystemId%'

order by t1.DateCreated

   

    SET NOCOUNT OFF


Function
  Public Function Get_Cl_IssuesBySystemID(ByVal strSystemId As String) As DataView
      Dim objDs As New DataSet
      Dim objDv As New DataView
      Dim connect As New SqlConnection(strConnection)

      '***For CommanType.Text***
      Dim strSelect As String

      strSelect = "SELECT  t1.issueId, t1.IssueTitle,t1.DateCreated,t2.CustomFieldValue" & _
              " FROM dbo.IssueTracker_Issues t1" & _
             
" LEFT OUTER JOIN dbo.IssueTracker_ProjectCustomFieldValues t2" & _
              " ON t1.IssueId = t2.IssueId" & _
             
" Where t2.CustomFieldId=5 and t2.CustomFieldValue like '%" &
strSystemId & "%'" & _
              " order by t1.DateCreated"
      Dim cmdSelect As New SqlCommand(strSelect, connect)
      cmdSelect.CommandType = CommandType.Text
       '***End For CommanType.Text***


         '***For CommanType.StoredProcedure***
      'Dim cmdSelect As New SqlCommand("Get_Cl_IssuesBySystemID", connect)
      'cmdSelect.CommandType = CommandType.StoredProcedure
      'PARAM
      'cmdSelect.Parameters.Add("@SystemId", strSystemId)
       '***End For CommanType.StoredProcedure***

      Dim objAdapter As New SqlDataAdapter(cmdSelect)

      Try
        objAdapter.Fill(objDs)
        objDv = objDs.Tables(0).DefaultView

      Catch objErr As Exception
        Throw New Exception(objErr.Message)
      End Try
      Return objDv
  End Function

View 2 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

Touchy Proc

May 11, 2006

I have been looking at this that past 20 minutes and it seems fine to me. I am trying to create it on my host account which has web based SQL manager(blows) so I can't really see whats truly wrong with it. But it gives me the error below: MessageDate will be the date passed in and CreateDate is a field with DateTime type. All the fields are there.





Error -2147217900

 

Incorrect syntax near the keyword 'SELECT'.
CREATE PROCEDURE prcGetMessages   @MessageDate    DateTimeSELECT tm.Title, tm.CreateDate, tu.TeamNameFROM tblMessage tmJOIN tblUserInfo tu ON tu.peID = tm.peIDWHERE tm.CreateDate > @MessageDate
 
---------------------------------------------------------------------------------CREATE PROCEDURE prcGetMessages
 @MessageDate    DateTime
SELECT tm.Title, tm.CreateDate, tu.TeamNameFROM tblMessage tmJOIN tblUserInfo tu ON tu.peID = tm.peIDWHERE tm.CreateDate > @MessageDateORDER BY tm.CreateDate ASC

View 1 Replies View Related

USE DbName In Proc...

Dec 7, 2001

Need to have 'Use Master' statement in one stored procedure from a Admin database to execute some task only in Master database could be performed.

error: Use database statement is not allowed in a procedure or trigger.

Is there a way to bypass this error?
I know my last result will be to put this stored procedure in Master, which I really don't want to...

thanks for help
-D

View 2 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

Help_stored Proc

May 31, 2001

Hi,
I am trying to figure out how to make stored procedure independent of the order in which the variables are being passed to it? i.e is there anyway to have it so that the stored procedure will match based on name versus the order of the variables passed in
thanks
Rozina

View 1 Replies View Related

SQL / Extended Proc Dll&#39;s

Oct 25, 2000

I created an extended procedure DLL according to the guidelines set forth in the Books Online. I placed this DLL to be called in a trigger. The DLL fires, but some aspects of the dll fail. Is there a C lang. limitation when used with SQL server? I can do file i/o in the dll (open a file, write to it) but other functions such as C API functions FindWindow / SendMessage always fail. I have tested the DLL outside of SQL server and it works fine. Whats up, I can't beleive that Microsoft would write SQLServer where only certain functions of the DLL called by SQL server will work.. Any ideas would be appreciated! KT

View 1 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







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