T-SQL How To Deal With Results From Stored Proc
Jul 23, 2005
Try hard to become familiar with T-SQL.
Can anybodey tell me the best way to deal with set's provided by a
stored procedure. Til yesterday I thougt trapping set in temp table
using INSERT EXEC is a way out of this, but then I struggeled with
nested INSERT EXEC's.
What are all the system proc's good for if the results cannot be
evaluated? The approach of modular programming is to have code doing
similar things in one place.
If I try to make use of sp_helprolemember to get login names for more
roles, pack the logins in one table and return the result set in a SP,
the procedure which calls that is unable to evaluate the set.
On the other hand I read the advice, not to access system tables
directly.
Is there a way out?
View 9 Replies
ADVERTISEMENT
May 14, 2008
Hi All,
I have a stored proc which is executing successfully...but the results of that stored proc are displaying in the Messages Tab instaed of results Tab. And in the Results Tab the results shows as 0..So, Any clue friends..it is very urgent..I am trying to call this stored proc in my Report in SSRS as well but the stored proc is not displaying there also...Please help me ASAP..
Thanks
dotnetdev1
View 4 Replies
View Related
Apr 19, 2006
I have this stored proc:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[usp_CrimRecTest]
-- Add the parameters for the stored procedure here
@caseID [nvarchar]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT dbo.tblCASENOMASTER.CASENO, dbo.tblCASEMAST.LASTNAME, dbo.tblCASEMAST.FRSTNAME
FROM dbo.tblCASENOMASTER LEFT OUTER JOIN
dbo.tblCASEMAST ON dbo.tblCASENOMASTER.CASENO = dbo.tblCASEMAST.CASENO
WHERE (dbo.tblCASENOMASTER.CASENO = @caseID)
END
When I run this with an EXEC statement, the result pane shows no results but the message pane says it completed successfully and one row is affected. I know my input data is good. I also get nothing when I call this sproc from a VB front end. Any ideas?
Thanks.
View 4 Replies
View Related
Jun 10, 2008
Hi guys. I'm trying to compare the results from various stored procedures. Various stored procedures in our database got redone (refactored), and I want to see if they still pull back the same data.
Are there any programs out there that do this? Compare result sets from stored procedures? Any help is appreciated. Thanks!
View 1 Replies
View Related
Aug 9, 2000
I have a search stored proc which fails to return results when called by more than one user.
I have put selects in the various SPs to trace results and if I call from 3 query windows (executnig each as quickly as I can work the mouse) I get the following:
1st query returns the trace values (including correct count of temp table recs) but no result set
2nd query erturns nothing just "The command(s) completed successfully."
3rd query returns full results.
This seems to be consistent.
We are running SQL Server 7 SP1.
Just upgrading to SP2 to see if that helps.
The main SP calls other SPs to build result sets.
These use quite a few temp tables passed between SPs, parse CSV lists, join different other tables, create a SQL string to exec to do the search and get record IDs to return (no cursors).
The result set is built by a called SP using the temp table with IDs to return.
Anyone know of any problems or can suggest anything to try?
View 3 Replies
View Related
Jul 20, 2005
Hi,I am using data from multiple databases and/or queries. It would greatlysimplify and speed things up if I could use CONTAINS in processing theresults. However, "CONTAINS" requires the data to be indexed. Due to theamount of processing, I think it would be faster even if I had to re-indexevery time.For example, I would like to do something like this (simplified toillustrate the desired functionality... This should show all of the wordsfrom one table that are not contained in their current or inflectional formswithin another table):SELECT W1.ContentFROM(SELECT Word AS ContentFROM MyTable) W1LEFT OUTER JOIN(SELECT Phrase AS ContentFROM MyOtherTable) W2ON W2.Content CONTAINS(INFLECTIONAL, W1.Content)WHERE W2.Content IS NULLCan the results of a procedure be indexed? If not, can I drop the resultsinto a new table and trigger an automatic index of it, pausing the procedureuntil the indexing is done?Or, it there another way?Thanks!
View 2 Replies
View Related
Nov 18, 2006
Hi,I have written a stored procedure which includes a DATEPART command, i.e.DATEPART(weekday, <date>)The result when ran from SQL Query Analyser is as expected . i.e. Sundayreturns 1, Monday 2, etcWhen the same proc is called from within the Access 2000 project Sundayis returned as 7, Saturday as 6 instead of 1 and 7 respectively.Basically the same stored proc returns different data depending on fromwhere it has been called.This is causing some issues obviously as the resulting tables andreports are showing incorrect data when presented in Access 2000.Has anyone else experienced this before or have any idea what may cause it.Regards,PB
View 1 Replies
View Related
Sep 13, 2005
I can get following data information from table1Name DescriptionA AviationA01 Aviation GeneralCA01 CapitalCA01-006 NEW CTB SignageA02 CapitalCA05-005 East End RoadwayCA05-006 Modify RoadHow do I write stored procedure to get following results based on the data from table1Code Name Description Level 1 A Aviation 1 1.01 A01 Aviation General 2 1.01.01 CA01 Capital 3 1.01.01.006 CA01-006 NEW CTB Signage 4 1.02 A02 Capital 2 1.02.01 CA05-005 East End Roadway 3 1.02.01.006 CA05-006 Modify Road 4Thank a lot!
View 1 Replies
View Related
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
Dec 5, 2007
Hi,
Does anyone know how to deal with image data type within stored procedure when using SQL2000?
I have a table and there is an image data type column. In this table I need to make a copy of one row within a table through a SP /it means to retrieve the whole row within SP, change another columns data (but, that the image is not modified) / and save the modified row back to the same table.
Problem is, that within SP is not alowed to use local varaibles of image data type. Does anyone know a solution for this? Please.
Thank you.
View 1 Replies
View Related
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
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
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
Jan 12, 2007
Hello,
I have tried to use a DaraReader Source to execute a SQL Server 2000 proc, and send the results to an Excel Destination.
The proc is named spv_CDGetFuelManDuplicates. Within the proc, temporary tables are created and dropped. When I attempt to set 'Exec dbo.spv_CDGetFuelManDuplicates' for the SqlCommand property of the DataReader, I get an error message indicating that my temporary tables are invalid objects.
The DaraReader is using an ADO.NET Connection Manager.
Should I be using an Execute SQL Task instead?
Thank you for your help, the proc code is below.
cdun2
********************************
alter PROCEDURE dbo.spv_CDGetFuelManDuplicates
AS
--Isolate Accounts and Contact Info per the View criteria
SELECT
RTRIM(Acctcode) AS AcctCode,
RTRIM(CompanyName)AS CompanyName,
MMS#, RTRIM(Phone) AS Phone
INTO #CorrectAcctCodes
FROM dbo.DailyAccountsDownload
WHERE
(AcctCode BETWEEN 'IQ001' AND 'IQ999') OR
(AcctCode BETWEEN 'QI001' AND 'QI999') AND (AcctCode NOT IN ('QI125', 'QI601', 'QI603', 'QI613', 'QI623', 'QI653', 'QI697')) OR
(AcctCode BETWEEN 'DQ001' AND 'DQ999') AND (AcctCode NOT IN ('DQ011')) OR
(AcctCode BETWEEN 'FQ002' AND 'FQ999') AND (AcctCode <> 'FQ011')
--Isolate duplicate phone numbers
--per the view criteria
SELECT
RTRIM(Phone)AS Phone, COUNT(*) AS RecordCount
INTO
#DupPhoneNumbers
FROM
dbo.DailyAccountsDownload
WHERE
(AcctCode BETWEEN 'IQ001' AND 'IQ999') OR
(AcctCode BETWEEN 'QI001' AND 'QI999') AND (AcctCode NOT IN ('QI125', 'QI601', 'QI603', 'QI613', 'QI623', 'QI653', 'QI697')) OR
(AcctCode BETWEEN 'DQ001' AND 'DQ999') AND (AcctCode NOT IN ('DQ011')) OR
(AcctCode BETWEEN 'FQ002' AND 'FQ999') AND (AcctCode <> 'FQ011')
GROUP BY
RTRIM(Phone)
HAVING
(COUNT(*)>1)
--Isolate duplicate Company Names
--per the View criteria
SELECT
RTRIM(CompanyName)AS CompanyName,
COUNT(*) AS RecordCount
INTO
#DupCompanyName
FROM
dbo.DailyAccountsDownload
WHERE
(AcctCode BETWEEN 'IQ001' AND 'IQ999') OR
(AcctCode BETWEEN 'QI001' AND 'QI999') AND (AcctCode NOT IN ('QI125', 'QI601', 'QI603', 'QI613', 'QI623', 'QI653', 'QI697')) OR
(AcctCode BETWEEN 'DQ001' AND 'DQ999') AND (AcctCode NOT IN ('DQ011')) OR
(AcctCode BETWEEN 'FQ002' AND 'FQ999') AND (AcctCode <> 'FQ011')
GROUP BY
RTRIM(CompanyName)
HAVING
(COUNT(*)>1)
--Result with duplicate company or phone numbers
SELECT
Acctcode, CompanyName, MMS#, Phone
FROM
#CorrectAcctCodes
WHERE
Phone IN (SELECT Phone FROM #DupPhoneNumbers)
OR
CompanyName IN (SELECT CompanyName FROM #DupCompanyName)
ORDER BY
Phone, companyname
--Drop the temp tables
DROP TABLE #CorrectAcctCodes
DROP TABLE #DupCompanyName
DROP TABLE #DupPhoneNumbers
View 2 Replies
View Related
Jun 13, 2006
I keep running into a problem with the debugger freezing up. I'm trying to pull results from a query into an array (not more than 10 rows)
cmd.ExecuteReader();
while (rdr.Read() == true) {
letter = rdr[0].ToString();
i += 1;
}
if I comment out the "letter = rdr[0].ToString();" portion of code, the project runs fine. With that code there, I can not step into the proc from the SQL test script. No errors are raised. VS just freezes.
View 1 Replies
View Related
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
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
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
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
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
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
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
Jun 13, 2007
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
View 3 Replies
View Related
Mar 26, 2008
Hello
I'm start to work with SSIS.
We have a lot (many hundreds) of old (SQL Server2000) procedures on SQL 2005.
Most of the Stored Procedures ends with the following commands:
SET @SQLSTRING = 'SELECT * INTO ' + @OutputTableName + ' FROM #RESULTTABLE'
EXEC @RETVAL = sp_executeSQL @SQLSTRING
How can I use SSIS to move the complete #RESULTTABLE to Excel or to a Flat File? (e.g. as a *.csv -File)
I found a way but I think i'ts only a workaround:
1. Write the #Resulttable to DB (changed Prozedure)
2. create data flow task (ole DB Source - Data Conversion - Excel Destination)
Does anyone know a better way to transfer the #RESULTTABLE to Excel or Flat file?
Thanks for an early Answer
Chaepp
View 9 Replies
View Related
Dec 7, 2007
It's because of this one or maybe more people that the whole world of the internet becomes so much of crappy messy place... *sighs*
Since there is really no specifics on deleting a topic, this is the best I could do.
View 7 Replies
View Related
Jul 23, 2005
Hi, How can I store a stored procedure's results(returning dataset) intoa table?Bob*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
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
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
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
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
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
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
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
