Stored Proc Creation In A Single T-SQL

Oct 8, 2007

I have a requirement where i have to create diffferent stored procedures in different databases based on some criteria. The stored procedure creation scripts
will be in a single T SQL variable.Here is something i trying to achieve.( this is just a sample)USE MASTER

GO

declare @SQLString Nvarchar(1000)

declare @STR Nvarchar(1000)

declare @DBName nvarchar(100)

SET @DBName ='DB1'SET @STR='

create proc sptemp

@id int

as

select * from orders where id=@id

go

'

set @SQLString='USE ' + @DBName + char(10) +'GO'

set @SQLString =@SQLString + @str

EXEC (@SQLString)

When i try to run this script in master it is giving me the following errors:

Msg 170, Level 15, State 1, Line 2

Line 2: Incorrect syntax near 'GO'.

Msg 111, Level 15, State 1, Line 3

'CREATE PROCEDURE' must be the first statement in a query batch.

Msg 137, Level 15, State 2, Line 6

Can anyone help me out in this? 

 

Must declare the variable '@id'.

View 2 Replies


ADVERTISEMENT

Stored Proc Creation Script In T-Sql

Oct 8, 2007

I am trying to do the following: I need to execute a t-sql statement that contains the stored procedure creation script.
In reality this will have differnt stored procedure creation scripts which will be created in different databases depending upon certain criteria.
The example below is just for demo purposes. when i try to execute this in master it is showing me the following errors:


USE MASTER

GO

declare @SQLString Nvarchar(1000)

declare @STR Nvarchar(1000)

declare @DBName nvarchar(100)

SET @DBName ='BTGenesisMDRockville'

SET @STR='

create proc sptemp

@id int

as

select * from orders where id=@id

go

'

set @SQLString='USE ' + @DBName + char(10) +'GO'

set @SQLString =@SQLString + @str

EXEC (@SQLString)




Msg 170, Level 15, State 1, Line 2

Line 2: Incorrect syntax near 'GO'.

Msg 111, Level 15, State 1, Line 3

'CREATE PROCEDURE' must be the first statement in a query batch.

Msg 137, Level 15, State 2, Line 6

Must declare the variable '@id'.


Can anyone help me on this?

View 7 Replies View Related

Stored Proc How To Return A Single Value

Oct 9, 2007

How do I return a value in a stored procedure? I want to return a value for TheQuarterId below but under all test conditions am only getting back a negative one. Please help! create PROCEDURE [dbo].[GetQuarterIdBasedOnDescription]
(
@QuarterString nvarchar(10),
@TheQuarterId int output
)
AS

BEGIN
SELECT @TheQuarterId = QuarterId from Quarter WHERE Description=@QuarterString
END

 

View 1 Replies View Related

Escaping Single Quote In Stored Proc With Parameter..

Feb 26, 2008

We have a .NET drop down, which gets populated as the user types in letters(last name). If the user types in the single quote we get the error about not escaping the single quote. Question is, which way would it be easier to fix, in the .NET code or in the SQL procedure? I am not to sure if we have full access to the source code since that is a 3rd party control, so if that is not feasible how would I fix that in the stored procedure? This is the current proc that we are using:




Code Snippet
select @str = 'SELECT DISTINCT TOP ' + @Top + ' e.DisplayName
as DbComboText,
e.EmployeeID as DbComboValue
FROM DepartmentDirectory.dbo.Employees ee
INNER JOIN DataMart.dbo.Employees e ON ee.UIN = e.UIN
WHERE e.LastName like ''' + @LastName + ''' AND e.FirstName like ''' + @FirstName + '''
ORDER BY e.DisplayName'




Any help is greatly appreciated.

View 5 Replies View Related

Multiple Select Statements In Single Stored Proc

May 19, 2008



Hi,

I have used several sql queris to generate a report. This queries pull out data from different tables. But sometimes at the same table too.
Basically those are SELECT statements.
I have created stored proc for each SELECT statement. now I'm wondering can I include all SELECT statements in one stored proc and run the report.
If possible, can anyone show me the format?

Thanks

View 4 Replies View Related

Stored Proc To Get Single Person From A Table Based On Earliest Datetime

Oct 13, 2005

Hi,

I'm having problems with a stored procedure, that i'm hoping someone can help me with.

I have a table with 2 columns - Username (varchar), LastAllocation (datetime)

The Username column will always have values, LastAllocation may have NULL values. Example

Username | LastAllocation
------------------------
Greg | 02 October 2005 15:30
John | 02 October 2005 18:00
Mike | <NULL>

My stored procedure needs to pull back a user name with the following criteria:

If any <NULL> dates send username of first person where date is null, sorted alphabetically, otherwise send username of person with earliest date from LastAllocation

Then update the LastAllocation column with GETDate() for that username.

This SP will be called repeatedly, so all users will eventually have a date, then will be cycled through from earliest date. I wrote an SP to do this, but it seems to be killing my server - the sp works, but I then can't view the values in the table in Enterprise Manager. SP is below - can anyone see what could be causing the problem, or have a better soln?
Thanks
Greg
------------------------------------------------------------------------------
------------------------------------------------------------------------------
CREATE PROCEDURE STP_GetNextSalesPerson AS
DECLARE @NextSalesPerson varchar(100)

BEGIN TRAN

IF (SELECT COUNT(*) FROM REF_SalesTeam WHERE LeadLastAllocated IS NULL) > 0
BEGIN
SELECT TOP 1 @NextSalesPerson = eUserName FROM REF_SalesTeam WHERE LeadLastAllocated IS NULL ORDER BY eUserName ASC
END
ELSE
BEGIN
SELECT TOP 1 @NextSalesPerson = eUserName FROM REF_SalesTeam ORDER BY LeadLastAllocated ASC
END

SELECT @NextSalesPerson
UPDATE REF_SalesTeam SET LeadLastAllocated = GETDATE() WHERE eUserName = @NextSalesPerson


COMMIT TRAN
GO

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

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

Stored Procedure Creation

Mar 5, 2007

I have created a database and I a table. Now I want to create a stored procedure.
I go to Programability=>Stored Procedures in my database and press right click=>New Stored Procedure. After I write the procedure I don't understand how to save it inside the datebase and execute it to preview the results in the Management Studio Express.

View 4 Replies View Related

Batch Creation Of Stored Procedures

Jan 14, 2004

I'm STUMPED on this one - I'm able to execute any type of create command just fine in a batch (SqlCommand object executing a text file stuffed with sql from Sql script, replacing all the GO statements with semicolins)

But when It gets to stored procedures, I get an "Incorrect syntax error"

It works ok if I execute one "create procedure" at a time -

View 4 Replies View Related

View Creation Using Stored Procedure

Apr 21, 2001

Hi

I need to create a view using a stored procedure .

The task is to Upload multiple sql server tables sourcing data from flat files as well as SQL server tables .It is the process of Data migration.
After loading few tables,I need to create a view on thoes tables which can be used (queried )to load furthe tables.

I need to AUTOMATE THIS PROCESS .Means Once I schedule the job .It should take fire the stored procedures one after another .
I am thinking to create a view though a stored procedure .
You can suggest me alternate ways to do same .


Sujit

View 1 Replies View Related

Stored Procedure Creation Date

Jun 14, 2001

Is there any system stored procedure or any table that contains the creation date of
stored procedures in one database . I really want to know wich is the last stored procedure
created in a database.
Thanks.

View 1 Replies View Related

Creation Of Two Tables In A Stored Procedure

Oct 17, 2006

Hi,

I was wondering if there was a way to create two temp tables within the same stored procedure.

Can we have two create statements one after the other?

Thanks


View 2 Replies View Related

Stored Procedure Creation,Invalid Object Name

May 25, 2008



I am trying to create a new stored procedure in Sql server managment studio express in a database.
I am getting an error message saying

Invalid object name 'Consumer_delete'.

Can you please tell why I am getting this error message?? Also , I need to make sure that the created procedure appears in the list of database objects after execution.
Thanks for your help



SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE Consumer_delete

@ConsumerID int,

@BusinessId int

AS

BEGIN

DECLARE @intError int

DECLARE @ConsBusinessID int

SET NOCOUNT ON

SELECT @ConsBusinessID = CONSUMERBUSINESS.[ID]

FROM CONSUMERBUSINESS

WHERE ConsumerID = @ConsumerID and BusinessId = @BusinessId

DELETE FROM CONSUMERBUSINESS

WHERE ConsumerID = @ConsumerID and BusinessId = @BusinessId



DELETE FROM CUSTOMERREMINDER

WHERE ConsumerBusinessID = @ConsBusinessID

DELETE FROM NOTE

WHERE ConsumerBusinessID = @ConsBusinessID

DELETE FROM VISIT

WHERE ConsumerBusinessID = @ConsBusinessID



--ERROR HANDLING--------

SET @intError = @@ERROR

IF @intError <> 0

GOTO ExitError

RETURN 0

ExitError:

RETURN @intError

END

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

Need A Help For Executing Script File Having Creation Of Stored Procedure From C#

Jan 9, 2008

 Hi,While executing script file from c# i have faced a problems like 1)Error at Go statement2) Must declare a scalar variable "@prmEvent" The following approach i have followed to executing script file. SqlConnection conn = new SqlConnection(); conn.ConnectionString = "server=localhost;initial catalog=Parish;Integrated Security=SSPI;";conn.Open(); string commandText = GetCommandText("Script file name"); SqlCommand databaseCmd = new SqlCommand(commandText, conn); databaseCmd.ExecuteNonQuery(); GetCommandTex()  method reads the script file from starting to end and returns script text.  My script file having following script.set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGoif exists (select 1 from dbo.sysobjects where id = Object_id('dbo.[prcSearchEvents]') and (type = 'P' or type = 'RF'))begin drop proc dbo.[prcSearchEvents]endGoCREATE PROCEDURE [dbo].[prcSearchEvents]@prmStartDate datetime,  @prmEndDate datetime,@prmEvent char(1)ASBEGIN    SELECT Title,FirstName,MiddleName,LastName,S.SexName as Sex, (CASE @prmEvent WHEN 'B' THEN DateOfBirth             WHEN 'D' THEN DateOfDeath             WHEN 'M' THEN DateOfMarriage         END ) as EventDate    from dbo.Parishioner P    left outer join dbo.Sex S on P.SexId = S.SexId    where (CASE @prmEvent WHEN 'B' THEN DateOfBirth             WHEN 'D' THEN DateOfDeath             WHEN 'M' THEN DateOfMarriage         END )    between @prmStartDate and @prmEndDate   END  But when i executing script file  having creation of stored procedure , i got such problem but other cases ( having normal sql commands) did not get any problm 

View 1 Replies View Related

Ignore Compilation Errors For Creation Of Stored Procedures

Apr 2, 2008

I have an application that is moving from an home made full text search engine to using the full text indexing engine of SQL 2005. I have a stored procedure that I want to behave as:
check documents table to determine whether a full text index for SQL's full text engine has been created.
If it has not, query the documentText table (which is the table for my in-house full text search)
If it has, use the full text indexing engine

My problem is that compilation of the TSQL to create the stored procedure fails when the full text index has not already been created with the followign error:


Msg 7601, Level 16, State 2, Procedure My_FullTextSearch, Line 0

Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'Documents' because it is not full-text indexed.

In my test lab, I tried:
1. creating the full text index
2. creating the stored procedure
3. deleting the ful text index
which gets me to the desired end result of having a stored procedure that can determine whether or not the full text index has been created yet (the procedure works in this state). But I creating this index as part of this stored procedure creation in production is not an option.

My question - Can I somehow tell SQL to ignore the compilation errors it encounters while creating this stored procedure? If not, is there some other way to create this "smart" stored procedure?

Here's a code snippet stripped down to the bare minimum to generate the error:



CREATE PROCEDURE [My_FullTextSearch]

@Term VarChar(1000)

AS

BEGIN

SET NOCOUNT ON;

IF NOT OBJECTPROPERTY(OBJECT_ID('Documents'), 'TableHasActiveFulltextIndex')=1

BEGIN

Select [DocumentID]

from [DocumentText]

where [Term] like '%' + LTRIM(@Term) + '%'

END

ELSE

BEGIN

Select [key] from FREETEXTTABLE(Documents, Contents, @Term)

END

END

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

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







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