MS SQL Server :: SELECT Query Stmt Inside Stored Procedure

How To Use A Stored Procedure Inside Select Query (sql Server Version 8)

Hi,
 Please help me in this problem...
 i am new to sql server..
 i am using sql server version 8...(doesnot support function with retun values..)
 so i have created a procedure...
 -----------procedure------------------(to find next monday after 6 months)-------------------
[code]
create proc next_Monday ( @myDate DATETIME )
as
BEGIN
set @myDate = dateadd(mm, 6, @myDate)
while datepart(dw,@myDate) <> 2
begin
 set @myDate = dateadd(dd, 1, @myDate)
end
 select @myDate
end
go
[/code]
 --------------------------------------------------------
 i can able to execute this procedure separately.... working well...
 but don't know how to call it inside another query....
the following throws error....
select smaster.sname, smaster.Datex, 'xxx'=(execute next_monday smaster.Datex) from smaster
 please help me... how to fix this problem...



View Replies ! View Related

Differentiate Between Whether Stored Procedure A Is Executed Inside Query Analyzer Or Executed Inside System Application Itself.
Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it?

What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results.
Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application.

However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password.

Looking forward for replies from expert here. Thanks in advance.

Note: Hope my explaination here clearly describe my current problems.

View Replies ! View Related

Stored Procedure Output Parameter Inside Select...
Does anyone know how can I (or can I) use a stored procedure output parameter(s) inside Select statement. For example

Select abc, cde, 'xyz' = Case
When 'aaa' then {output parameter of my stored procedure with 'aaa' as input parameter}
When 'bbb' then {output parameter of my stored procedure with 'bbb' as input parameter}
end
from MyTable

Thanks
Arcady

View Replies ! View Related

How To Query Two MS SQL DB's On The Same Server Inside A Stored Procedure
Okay, so I have a problem and I would be REALLY grateful for anyassistance anyone can offer because I have found little or no help onthe web anywhere.I want to access and do joins between tables in two different SQL db'son the same server. Heres what Im dealing with.In one database resides all of my security features for our clients,where it decides who can login, etc etc....In another database, I need to cross reference with a few fields in mysecurity db.See the issue Im running into here is that because the way the peoplehave their databases set up for different products, I would normallyhave to put these tables with security features in every database...which is horrible, because every time I do an update I would have todo it in 12 different places. Thats not efficient at all.So I thought if I had one central DB, where all security features arecontrolled from, that would be perfect... now the issue is crossreferencing and doing joins with other tables that ARENT in the samedb....have I lost you yet?I appreciate all of your help!THANKS!!

View Replies ! View Related

Query From Parameters Inside A Stored Procedure
Hi,

I have to write a stored procedure what repair a table.
It have to delete lost rows before make relation to itself (PK column is 'Kw_KeywordID', FK column is 'Kw_ParentID').
I do not found the way to write this procedure to can create statement from parameter of the procedure.
I would like to pass the table name, but I receive error if the query like 'Select ... Form @Table ....'.
If the parameter is in the 'WHERE', nothing problem.

My procedure is:
CREATE PROCEDURE sp_Repair_IS_KW_AbtKz176
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

declare @OrphanRowCount int
select @OrphanRowCount = 1

while(@OrphanRowCount > 0)
begin
  DECLARE KWCursor CURSOR FOR
  SELECT count(*) AS cRowCount FROM IS_KW_AbtKz176 WHERE Kw_ParentID IS NOT NULL AND Kw_ParentID NOT IN (SELECT Kw_KeywordID FROM IS_KW_AbtKz176);

  OPEN KWCursor;
  FETCH NEXT FROM KWCursor INTO @OrphanRowCount
  CLOSE KWCursor
  DEALLOCATE KWCursor

  IF (@@FETCH_STATUS = 0) AND (@OrphanRowCount > 0)
     BEGIN
   exec('DELETE FROM IS_KW_AbtKz176 WHERE Kw_ParentID IS NOT NULL AND Kw_ParentID NOT IN (SELECT Kw_KeywordID FROM IS_KW_AbtKz176)')
     END
end
END
GO

How can I run fully parameterized queries from an SP.
I can make it only like exec('DELETE FROM' + @TableName + ...)

Thank you for any idea,
Imre

View Replies ! View Related

Calling A Stored Procedure Inside Another Stored Procedure (or &"nested Stored Procedures&")
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?

View Replies ! View Related

MSSQL Stored Procedure Query Using SELECT TOP
Is there a way to do a SELECT TOP # using a variable for the #?
In other words I'm doing a SELECT TOP 50* FROM DATATABLE
If I pass an @value for the number
SELECT TOP @value* FROM DATATABLE doesn't work
I am generating a random sampling of data and I want to allow the user to select the number of results they choose to have.
Thanks in advance.

View Replies ! View Related

Stored Procedure That Return Dataset(Select Query)

Hi all,

I have a SP that return a dataset and I was thinking to execute that SP inside of other SP then catch the dataset to put into a variable or put into a temp table. What I know is you can not use recordset on output and input parameter in SP correct me if im wrong. I'm just wondering if I there is a work around in this scenario.

Thanks and have a nice day to all.

View Replies ! View Related

Select Inside Update Query?
Is it possible for me to do something like

update table1 SET var1=something,var2=something2
from table1
(SELECT * from table2) as newtable
where newtable.field1=acondition

View Replies ! View Related

MS SQL Stored Procedures Inside Another Stored Procedure
Hi,
Do you know how to write stored procedures inside another stored procedure in MS SQL.

Create procedure spMyProc inputData varchar(50)
AS
----- some logical

procedure spMyProc inputInsideData varchar(10)
AS
--- some logical
--- go
-------

View Replies ! View Related

{fn CURDATE()} Inside Stored Procedure
Hi,I created the stored procedure in SQL Server 2000CREATE PROCEDURE RsaTestASSELECT {fn CURDATE()}and to my surprise found out that it worked..To my surprise, because all the documentationthat I find suggests that {fn CURDATE()} isan ODBC escape clause..I can understand why/how ODBC escape clauseswork in queries, but not how this would workinside a stored procedure.I created the sp by running the above code inqueryanalyzer. sp_helptext 'RsaTest' shows thatthe {fn CURDATE()} is still in there, so itdoesn't appear to get transformed in to SQLServer specific code by whatever means queryanalyzer uses to connect to the database..Can anyone explain how this works? Or whereto find more info on {fn } syntax inside storedprocedures?Cheers,Rsa

View Replies ! View Related

If Statement Inside A Stored Procedure!!!!
hi all,
i'm wondering if i can use one stored procedure in too cases, this is the senario:
i have stored procedure for admin and another one for user, they have the same select everything is the same except that in admin SP i have where @admin = admin and for user i have where @user = user
if there a way to use if and else to make this happen
this is what i did so far:

CREATE PROCEDURE [test] @admin INT, @user INT, @indexType INT as
if @indexType = 1

begin

SELECT * FROM table WHERE something IN (SELECT * FROM anothertable where admin = @admin)
end
else
begin
SELECT * FROM table WHERE user = @user
end
GO

any suggestion will be very helpful
thanks

View Replies ! View Related

How Can I Use A Stored Procedure Call Inside The WHERE?

Hi folks
I nead to call a stored procedure in a where statemene, but MSSQL dont like that.

My problem is that the StoredProcedure is calling itself recursive and therfore its impossible to add the code as a standard SELECT statement. Here is the code

ALTER PROCEDURE dbo.advsp_FilterRecordRights
@RequesterGUID Char(20),
@EntityGUID Char(20)
AS
BEGIN
if not Exists(Select *

from GUIDRightsH
where GUIDRightsH.EntityGUID = @EntityGUID and

GUIDRightsH.RequesterGUID = @RequesterGUID and
GUIDRightsH.RecProp <> 0)
Begin

Return 1
end

if not Exists(Select *

from UsergroupMembers
where UserGroupMembers.UsergroupGUID = @RequesterGUID and
dbo.advsp_FilterRecordRights(UserGroupMembers.UserGUID,@EntityGUID) = 1)
Begin

Return 1
end

Return 0

END

View Replies ! View Related

RaiseError Inside Stored Procedure
I want to execute some insert statements within a stored procedure and commit those changes regardless of any raiseerror that occurs later in the stored procedure. My difficulty is that I am forced to use raiseerror with severity 16 in order to send a message through the powerbuilder application interface (compiled vendor code). I have tried save points but can't get that to save my insert and still present an error to the user about something else that happens later.

here is an example

BEGIN procedure

Insert something and save it even if error is raised below

RaiseError('you made a mistake and need to do this.',16,-1)

END procedure

View Replies ! View Related

Iterate Result Set Inside Stored Procedure
Hello, I have a situation that I query a table and return multiple rows (email addresses). I want to iterate through the rows and concatenate all email addresses into one string (will be passing this to another stored procedure to send mail). How can I process result rows inside a stored procedure? This is what I have so far: CREATE PROCEDURE [dbo].[lm_emailComment_OnInsert] @serviceDetailID int,@comment varchar(500),@commentDate DateTime,@commentAuthor varchar(100)ASBEGINDECLARE @serviceID intDECLARE @p_recipients varchar(8000)DECLARE @p_message varchar(8000)DECLARE @p_subject varchar(100)/* Grab the Service_id from underlying Service_Detail_id*/SELECT @serviceID = Service_id FROM lm_Service_Detail WHERE Service_Detail_id = @serviceDetailID/* Get email addresses of Service Responsible Parties */
SELECT DISTINCT dbo.lm_Responsible_Party.EmailFROM dbo.lm_Service_Detail INNER JOIN
dbo.lm_Service_Filing_Type ON dbo.lm_Service_Detail.Service_id = dbo.lm_Service_Filing_Type.Service_id INNER JOIN
dbo.lm_Responsible_Party_Filing_Type ON dbo.lm_Service_Filing_Type.Filing_Type_id = dbo.lm_Responsible_Party_Filing_Type.Filing_Type_id INNER JOIN
dbo.lm_Responsible_Party ON dbo.lm_Responsible_Party_Filing_Type.Party_id = dbo.lm_Responsible_Party.Party_idWHERE (dbo.lm_Service_Detail.Service_Detail_id = @serviceDetailID)/* Build message */
SET @p_subject = "KLM - Service ID: " + CAST(@serviceID AS varchar(4))SET @p_recipients = "" /*need string of addresses*/

SET @p_message = @p_message + "Service Detail ID: " + CAST(@serviceDetailID AS varchar(4)) + char(13)SET @p_message = @p_message + "Comment Date: " + CAST(@commentDate As varchar(25)) + char(13)SET @p_message = @p_message + "Comment Author: " + @commentAuthor + char(13)SET @p_message = @p_message + "Comment: " + @comment + char(13)PRINT "subject: " + @p_subject + char(13)PRINT "recip: " + @p_recipients + char(13)PRINT "msg: " + @p_message + char(13)/*Send the email*/
Execute master..xp_sendmail @recipients = @p_recipients, @message = @p_message, @subject = @p_subject END
GO

View Replies ! View Related

Using Joins Inside Stored Procedure With Row_Number
This example is working:
Declare @startRowIndex INT; set @startRowIndex = (@PagerIndex * @ShowMembers); With BattleMembers as ( SELECT TOP 20 ROW_NUMBER() OVER (ORDER BY LastActivityDate DESC) AS Row, UserId, UserName FROM aspnet_Users) SELECT UserId, UserName FROM BattleMembers WHERE Row between @startRowIndex and @startRowIndex+@ShowMembersEND
and this one doesn't work:USE [xx]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[battle_Paging]@PagerIndex INT,@ShowMembers INT ASBEGINDeclare @startRowIndex INT; set @startRowIndex = (@PagerIndex * @ShowMembers); With BattleMembers as ( SELECT TOP 20 ROW_NUMBER() OVER (ORDER BY aspnet_Users.LastActivityDate DESC) AS Row, aspnet_Users.UserId, aspnet_Users.UserName, mb_personal.Avatar FROM aspnet_Users LEFT JOIN mb_personal ON (mb_personal.UserID=aspnet_Users.UserId) SELECT UserId, UserName, Avatar FROM BattleMembers WHERE Row between @startRowIndex and @startRowIndex+@ShowMembersEND
Error:Msg 156, Level 15, State 1, Procedure battle_Paging, Line 18Incorrect syntax near the keyword 'SELECT'.
I try to join in the table mb_personal here. Some help would be very appreciated! Thanks.

View Replies ! View Related

Comparing Two Strings Inside A Stored Procedure
Basically I have two strings. Both strings will contain similar data because the 2nd string is the first string after an update of the first string takes place. Both strings are returned in my Stored Procedure
For example:String1 = "Here is some data. lets type some more data"String2 = "Here's some data. Lets type some data here"I would want to change string2 (inside my Stored Procedure) to show the changed/added text highlighted and the deleted text with a strike though.
So I would want string2 to look like thisstring2 = "Here's <strike>is</strike> some data. Lets type some <strike>more</strike> data here"
Is there an way to accomplish this inside a stored procedure?

View Replies ! View Related

Executing A Variable Inside A Stored Procedure
Hello :) I need to do something like this:

CREATE PROCEDURE SelectCostumers @name varchar(100)
Declare @SQL = "SELECT Id, Name FROM Costumers"
AS
IF (@name IS NULL)
@SQL
ELSE
@SQL += "WHERE Name LIKE @name"

See, what I need is a string variable that I can concatenate with whatever I want depending on the parameter I get.

Thank you

View Replies ! View Related

How To Dynamically Create SQL Inside A Stored Procedure?
I am having problem with 'TOP @pageSize'. It doesn't work, but if I replace it by 'TOP 5' or 'TOP 6' etc., then the stored procedure runs without errors.
Can someone please tell me how I could use @pageSize here so that it dynamically determines the 'n' of 'TOP n' ?

ALTER PROCEDURE dbo.spGetNextPageRecords

(
@pageSize int,
@previousMaxId int

)

AS
/* SET NOCOUNT ON */
SELECT Top @pageSize ProductId, ProductName
FROM Products
WHERE (ProductID > @previousMaxId) order by ProductId
RETURN

View Replies ! View Related

Creating A Table Inside A Stored Procedure
I am trying to creating a table inside a stored procedure using SQL that works fine in Query Analyzer. However, when I check the syntax I get the following message:

Error 208: Invalid object name '##OPTIONSEX'

I am using the following SQL script:

CREATE PROCEDURE [dbo].[Test2] AS

CREATE TABLE ##OPTIONSEX
(
OPTION_PLAN VARCHAR(50),
TOT_OPTIONS_EXCHANGED FLOAT NULL
)

GO

INSERT ##OPTIONSEX

SELECT
B.COMPONENT,
TOT_OPTIONS_EXCHANGED = SUM(A.UNITS)
FROM TBLEXERCISEOPTIONS A, TBLCOMPONENT B
WHERE B.COMPONENTID = A.COMPONENTID
GROUP BY B.COMPONENT

GO

Any help getting this to run correctly would be appreciated.

View Replies ! View Related

Debugging Stored Procedure Inside SSMSE !!!
Hi for all

i just get the news that the Dec. CTP of SQL Server 2005 & tools is released !

but my dream is to see debugging stored procedure support inside the SSMSE as debugging applications inside Visual Studio 2005

Many many many thanks to Microsoft and for the great efforts on the cool products

Mohamed

View Replies ! View Related

Commit Not Commiting Inside Stored Procedure
Hi Folks!

I have a cursor inside a stored procedure that should identify rows based on certain criteria and update the columns on those records.

While I try to do so, the cursor goes into an infinite loop although I have only 1 record that matches my criteria. I think that the transaction is not commited and the cursor is picking up the same record for update.

Here is my sample record in table :tblMsg
SenderRef <space> MsgStatusId <space> MsgType <space>Groupid
1281341<space> 1 <space>KBC-NON-ETC-MATCHED-BLIM <space>191902

1281341 <space>18 <space>KBC-RCVD-ADM <space>191902<space>

and here is my code.

CREATE PROCEDURE msg_ResolveADMBeforeBlim
AS

DECLARE @blimGroupId AS INT
DECLARE @admSenderRef AS VARCHAR(50)

DECLARE admCursor CURSOR FOR
SELECT senderref
FROMtblMsg
WHERE msgstatusid = 18
AND msgtype = 'KBC-RCVD-ADM'
FOR UPDATE

OPEN admCursor

FETCH NEXT FROM admCursor INTO @admSenderRef

WHILE @@FETCH_STATUS = 0
BEGIN
--FIND CORRESPONDING BLIM RECORD AND GRAB ITS GROUPID TO UPDATE THE ADM
SELECT @blimGroupId = GroupID
FROM tblMsg
WHERE msgType = 'KBC-NON-ETC-MATCHED-BLIM'
AND SenderRef = @admSenderRef
PRINT 'AFTER SELECT INSIDE WHILE: Senderref- >' + @admSenderRef;
--UPDATE THE ADM RECORD WITH THE GROUPID AND MSGSTATUS = 3
BEGIN
UPDATE tblMsg
SET MsgStatusId = 3, GroupId = @blimGroupId
WHERE CURRENT OF admCursor
END
PRINT 'AFTER UPDATE INSIDE WHILE';
END

CLOSE admCursor
DEALLOCATEadmCursor

The update statement was included beteween a BEGIN TRAN and END TRAN. But no joy.

Any ideas why this would happen?

Thanks for your help.
Arun

View Replies ! View Related

Executing A Query Inside A Procedure
Hello,
I have written a procedure and I have a variable that has been declared as a varchar. For example:
@test = 'select * from table_a'
I want to able to execute this @test varible. Any help will be greatly appreciated.
Thank you

View Replies ! View Related

Problem When Invoking Stored Procedure With Cursor From Inside .net
Dear all,i'm facing a problem with my storedprocedure which happened when i ran my web application and reach to the point where my class invoke this storedprocedure,my SP contains a cursor that built his sql according to certain condition, so i put the "SET @cur Cursor For....." inside the if block (definitely i've declared it under AS keyword directly) and this SP is working well inside sql server(I've tested it), BUT when my ASP.net code invoke this SP it gives me the following error : "The Variable @cur does not currently have a cursor allocated to it" repeated as much as there are IF clauses in my SP,Please Help.Regards,

View Replies ! View Related

How To Pass The Value Of A Text Box Inside A FORMVIEW To A SQL Stored Procedure
Hi,
I'm new to ASP.NET 2.0. I have a sqldatasource and a formview controls on a web page and inside the formview control I have two textboxes. When I click the UPDATE button in the formview, I'd like to be able to retrieve the values of the textboxes inside the formview control and pass these values to a 'Update' SQL stored procedure defined in the Sqldatasource.
Does anyone know how I can do this? Hope my question is clear.
Thanks in advance.
hakl

View Replies ! View Related

To Pass Data To An Stored Procedure Inside Sql Server
I want to use a stored procedure inside Sql Server from my aspx page so that the data entered in the form goes to the database after submit.

My stored procedure (inside the SQL Server) inserts several fields in a table and it returns two variables.
What code I need to write in order to pass the data from the form to the stored procedure inside the Sql Server? And to store the two returned values?

Thanks

View Replies ! View Related

Help-convert Date Inside Stored Procedure Problem
problem convet date

Code Snippet
DECLARE
@y_Date [varchar](4),@M_Date [varchar](2),@d_Date [varchar](2)
DECLARE @mydate [datetime]
set @y_Date='2008'
set @M_Date ='8'
SET @d_Date='05'
set @mydate = CAST(CAST(@M_Date + '-'+ @d_Date + '-' + @Y_Date AS nvarchar) AS DATETIME)
select @mydate

2008-05-08 00:00:00.000
and it look ok

but whan i do this

Code SnippetEXECUTE [dbo].[testpro] @mydate

the date not pass properly !!

Code Snippet
DECLARE
DECLARE @mydate [datetime]
set @mydate = getdate()

2008-05-08 01:20:35.870
like this it work ok

what is the problem with tis line ?
set @mydate = CAST(CAST(@M_Date + '-'+ @d_Date + '-' + @Y_Date AS nvarchar) AS

tnx

View Replies ! View Related

How To Create A Second Independent Transaction Inside A CLR Stored Procedure?
I use the context connection for the "normal" work in the CLR procedure.

View Replies ! View Related

Calling A Stored Procedure From ADO.NET 2.0-VB 2005 Express: Working With SELECT Statements In The Stored Procedure-4 Errors?
Hi all,

I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):

(1) /////--spTopSixAnalytes.sql--///

USE ssmsExpressDB

GO

CREATE Procedure [dbo].[spTopSixAnalytes]

AS

SET ROWCOUNT 6

SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName

FROM LabTests

ORDER BY LabTests.Result DESC

GO

(2) /////--spTopSixAnalytesEXEC.sql--//////////////

USE ssmsExpressDB

GO
EXEC spTopSixAnalytes
GO

I executed them and got the following results in SSMSE:
           TopSixAnalytes       Unit          AnalyteName
1          222.10                      ug/Kg        Acetone
2          220.30                      ug/Kg        Acetone
3          211.90                      ug/Kg        Acetone
4          140.30                      ug/L          Acetone
5          120.70                      ug/L          Acetone
6            90.70                      ug/L          Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")

Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)

sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure

'Pass the name of the DataSet through the overloaded contructor

'of the DataSet class.

Dim dataSet As DataSet ("ssmsExpressDB")

sqlConnection.Open()

sqlDataAdapter.Fill(DataSet)

sqlConnection.Close()

End Sub

End Class
///////////////////////////////////////////////////////////////////////////////////////////

I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined   (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)

Please help and advise.

Thanks in advance,
Scott Chang

More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.

View Replies ! View Related

How Do I Find Out If A SQL Statement Found Records Inside A Stored Procedure
I need to run a sequel statement in a stored procedure and get if it found any records because the next statements depend on if records were found or not. How do I do this?

View Replies ! View Related

Insertion And Deletion Of Records Inside A Single Stored Procedure
Hi,
I have two tables A and B. In table A i have three columns called empid, empname and empsalary where empid is an identity column. Table A has some records filled in it. Table B has the same schema except the fact that the empid is not an identity column in table B. Table B does not contain any rows initially. All other aspects remain the same as that of table A. Now i am going to delete some records in table A based on the empid. When i delete the records in table A based on empid the deleted records should be inserted into table B with the same empid. I need to accomplish these two tasks in a single stored procedure. How to do it? I need the entire code for the stored procedure. Please help me. I am trying for the past 4 days.
Thanx in Advance

View Replies ! View Related

Using Multiple Distinct Queries Inside Single Stored Procedure
Hello,

I was wondering if anyone can explain the positives and negatives of using a single stored procedure that contains one or more distinct queries. I know there are problems with dynamic SQL but I am not proficient enough to know whether this falls under that umbrella.

For clarification, what I am referring to is this: In a single stored procedure, I have a parameter called Query_ID that is used to identify which query in the sproc that I want to execute. Then from my ASP page, I simply pass the appropriate value for Query_ID. So:

IF @QUERY_ID = 1
    BEGIN
        SELECT [whatever]
        FROM [tbl1]
        WHERE [conditions]
        GROUP BY [something]
        ORDER BY [somethingelse]
    END
ELSE
IF @QUERY_ID = 2
    BEGIN
        SELECT [whatever]
        FROM [tbl2]
        WHERE [conditions]
        GROUP BY [something]
        ORDER BY [somethingelse]
    END
END

I hope that makes sense. Thanks in advance.

View Replies ! View Related

Using MatLab Built-in Statistical Functions Inside SQL Server Stored Procedure
Hi everybody,

I would like to use MatLab built-in statistical functions (beta, gamma, normal, etc.) from inside a SQLServer stored proceudre. Does anyone know if possible? (Of course, If so, where can I get documentation for doing this?)

Thanks in advance!

JorgeHG.

View Replies ! View Related

EXEC Inside CASE Inside SELECT
I'm trying to execute a stored procedure within the case clause of select statement.
The stored procedure returns a table, and is pretty big and complex, and I don't particularly want to copy the whole thing over to work here. I'm looking for something more elegant.

@val1 and @val2 are passed in

CREATE TABLE #TEMP(
tempid INT IDENTITY (1,1) NOT NULL,
myint INT NOT NULL,
mybool BIT NOT NULL
)

INSERT INTO #TEMP (myint, mybool)
SELECT my_int_from_tbl,
CASE WHEN @val1 IN (SELECT val1 FROM (EXEC dbo.my_stored_procedure my_int_from_tbl, my_param)) THEN 1 ELSE 0
FROM dbo.tbl
WHERE tbl.val2 = @val2

SELECT COUNT(*) FROM #TEMP WHERE mybool = 1

If I have to, I can do a while loop and populate another temp table for every "my_int_from_tbl," but I don't really know the syntax for that.

Any suggestions?

View Replies ! View Related

Looping Through Stored Procedure Inside Another Stored Procedure And Displaying The Category And Then Displaying 1 Item In Each Category
I used to do this with classic asp but I'm not sure how to do it with .net.Basically I would take a table of Categories, Then I would loop through those. Within each loop I would call another stored procedure to get each item in that Category. I'll try to explain, Lets say category 2 has a player Reggie Bush and a player Drew Brees, and category 5 has Michael Vick, but the other categories have no items.Just for an example.. Category Table: ID Category1 Saints2 Falcons3 Bucaneers4 Chargers5 FalconsPlayer Table:ID CategoryID Player News Player Last Updated1 1 Reggie Bush Poetry in motion 9/21/20062 1 Drew Brees What shoulder injury? 9/18/20063 5 Michael Vick Break a leg, seriously. 9/20/2006 Basically I would need to display on a page:SaintsReggie BushPoetry in MotionFalconsMichael VickBreak a leg, seriously.So that the Drew Brees update doesnt display, only the Reggie Bush one, which is the latest.I have my stored procedures put together to do this. I just don't know how to loop through and display it on a page. Right now I have two datareaders in the code behind but ideally something like this, I would think the code would go on the page itself, around the html.

View Replies ! View Related

Stored Procedure Inside A Stored Procedure
Hello,

How can I call stored procedure inside a stored procedure

Thankx

View Replies ! View Related

Select STMT
Hi

Can anyone help me in modifying this select statement in order to add two more columns to see if the column is identity and if the column is indexed.

SELECT SUBSTRING(SYSOBJECTS.NAME, 1, 45) AS TABLE_NAME,
SUBSTRING(SYSCOLUMNS.NAME, 1, 40) AS COLUMN_NAME,
SUBSTRING(SYSCOLUMNS.NAME, 1, 15) AS DATA_TYPE,
SYSCOLUMNS.LENGTH,
SYSCOLUMNS.XPREC AS PRECISIONS,
SYSCOLUMNS.XSCALE AS SCALE,
SYSCOLUMNS.ISNULLABLE AS ALLOW_NULLS

FROM SYSREFERENCES
RIGHT OUTER JOIN
SYSCOLUMNS INNER JOIN SYSTYPES ON SYSCOLUMNS.XTYPE = SYSTYPES.XTYPE
INNER JOIN
SYSOBJECTS ON SYSCOLUMNS.ID = SYSOBJECTS.ID ON
SYSREFERENCES.CONSTID = SYSOBJECTS.ID
WHERE SYSOBJECTS.XTYPE <> 'S' and SYSOBJECTS.XTYPE <> 'V' AND SYSOBJECTS.XTYPE <> 'P'
ORDER BY TABLE_NAME

thanks
Venu

View Replies ! View Related

Select Stmt
How do I use the select statement to create a blank field in a temp table, so I can use the update statement to populate the data later.

I tried [Select ' ' as field] and then tried the update statement to later populate data and I am getting the following error:
Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.

Please help

View Replies ! View Related

Why Won't This Work? Select Stmt
select uid, OrderID, Count(OrderID)As DupCnt
from OrdDetails
group by uid, OrderID
having count(OrderID) > 1

this returns no rows, can't I show another column to identify which uid goes with the dups, I did it before and now it doesn't work, probably something silly I'm missing.

thx,

Kat

View Replies ! View Related

Max Date Select Stmt Problem
Hello Everybody,I have a problem, with select stmt:SELECT TOP 15 *FROM oaVIEW_MainData AS TOP_VIEW,oaLanguageData_TAB AS RwQualifierJoin with (nolock)WHERE (c_dateTime>='2007.01.10 00:00:00' AND c_dateTime<='2007.01.1023:59:59')AND RwQualifierJoin.text_id = c_cfgRegPointAND (((RwQualifierJoin.local1 LIKE N'Position of any bubu')))AND TOP_VIEW.c_dateTime=(SELECT MAX(SUB_VIEW.c_dateTime)FROM oaVIEW_MainData AS SUB_VIEW,oaLanguageData_TAB ASRwQualifierJoin1 with (nolock)WHERE (c_dateTime>='2007.01.10 00:00:00' AND c_dateTime<='2007.01.1023:59:59')AND RwQualifierJoin1.text_id = c_cfgRegPointAND (((RwQualifierJoin1.local1 LIKE N'Position of any bubu')))AND TOP_VIEW.c_dsmIdent=SUB_VIEW.c_dsmIdent)order by c_dateTime descPlease consider:- top doesn't metter, if I will use one or 10000 result is always thesame.- oaVIEW_MainData, is a view on major big table, holding lot of recordsjoinden with small table containing configuration data, over left outerjoin; both tables are with nolock option,- quersy supose to return last record from major table/view, in giventime, additionaly, with other where conditions (like in this case withtext),- on major table, are indexes which one is on id field (not used inthis query at all), which is a pk clustered, and other is on dateEvt(c_dateTime) which is a desc index with fill level 90%- table has also other indexes, on three different fields, one oftheses is dsmIdent,Now, if I'm using max(id) works very fast, and ok for me, but theproblem is, I should not use id, because might be, that the recordswill be written in the table with random order, so the only one sayingwhich is newest, will be dateEvt.Using dateEvt as max(), dramaticly slows query, so I'm acctualy unableto get result. What is much more funny, server is totaly busy with thisquery, and it's procesor jumps on 100%.Now, because the query is builded dynamicly, by a user selections,that's why we decided on such a parser ... problem is, it is notworking :(Can I change index on dateEvt somehow, to sped this up?Maybe construct query somehow different, to get this over max() date?Please helpMatik

View Replies ! View Related

Number Of Rows In A Select Stmt
hey all,

I am writing a sproc:

select @strCourseNameRegFor = sal.CourseName , @strSectionNoRegFor = sal.SectionNo,
@dteStartDateRegFor = sal.StartDate, @dteEndDateRegFor = sal.EndDate,
@dteStartTimeRegFor = sal.StartTime, @dteEndTimeRegFor = sal.EndTime,
@strDaysOfWeekRegFor = sal.DaysOfWeek
from lars.dbo.tblSalesCourse as sal, lars.dbo.tblCourseCatalog as cat
where sal.SchoolYr = @intRegForYear and rtrim(sal.SchoolTerm) = rtrim(@strRegForTerm) and upper(rtrim(sal.CourseName)) = upper(rtrim(@strCourseNamePrev))
and cat.NewStuAllowed = 0 and sal.CourseName = cat.CourseName and sal.Cancelled <> 1 and cat.SchoolYr = sal.SchoolYr
and sal.MaxNoStudents > sal.CurrNoStudents

I want to check if the select returned an empty set or not. I cannot use @@rowcount because i am assigning the values to the local vars. I tried

if @strCourseNameRegFor is null
begin
set @err = 'No courses';
end

but for some reason even if there are any records in the set, the if condition is getting satisfied. Can anyone help?

View Replies ! View Related

How To Get Recordset From Only Last SELECT Stmt Executed???
How to get recordset from only last SELECT stmt executed???

this is part of my code.. in T-Sql

Create proc some mySp
...
AS
...
set nocount on
if (@SelUserID is null)
select 0
else if (@SelUserID = @userID)

select 1

else
begin

select * -- select a.
from dms_prsn_trx_log
where @incNo = ult_incid_no and
prsn_trx_no = 10 and
trx_log_txt = @logText
if (@@rowcount != 0)
set @isForwardedByUser = 1
select 2 -- select b. I NEED This value.

end
set nocount off
GO

here it executes select a, b.
But, I want mySp return last executed select result. which is here "select 2"

I thought set nocount ON does that, but it doesn't.
How can I do???

View Replies ! View Related

Generalized SP For Select Stmt : Better Option
Hi Everyone,

For my application, i am writing a generalized Sp for the Select Stmt.

I have started with as shown below,

Create procedure proSelect
@TabName varchar(1000),
@ColName varchar(1000),
@ConName varchar(1000)
As
Begin
Declare @str Varchar(8000)
If @ColName = '' Set @ColName = '* '
else Set @ColName = @ColName + ' '
Set @str = 'Select ' +@ColName+ 'From ' +@tabname
If @ConName <> ''
Set @str = @str + ' Where '+ @ConName
exec (@str)
End;

I wan to more generalize it.. so that all the functionalities of select stmt can be accomplished with this sp...

Can anyone help ???

Thanks in advance...

one more small doubt(personal): All these days i was a starting member of this forum,but today it has changed to 'Yak Veteran Posting'.. What does that mean.. If not to be disclosed in forum, Do mail me at
satishr@kggroup.com

Regards,
satish.r
"Known is a drop, Unknown is an Ocean"

View Replies ! View Related

Answered - Sum The Same Field Twice In Select Stmt
Hello friends , I have table (MoneyTrans) with following structure
[Id] [bigint] NOT NULL,
[TransDate] [smalldatetime] NOT NULL,
[TransName] [varchar](30) NOT NULL, -- CAN have values 'Deposit' / 'WithDraw'
[Amount] [money] NOT NULL
I need to write a query to generate following output 
Trans Date, total deposits, total withdrawls, closing balance 
i.e. Trans Date, sum(amount) for TransName='Deposit' and Date=TransDate , sum(amount) for TransName=Withdraw and Date=TransDate , Closing balance (Sum of deposit - sum of withdraw for date < = TransDate )
I am working on this for past two days with out getting a right solution. Any help is appreciated
Sara

View Replies ! View Related

@@RowCount To Display Rows From Select Stmt
I've created a Stored Procedure which performs a Select against my table, and displays the rows returned via these stmts -
@RowCount int Output
SELECT @rowcount = @@RowCount
This Works fine when Executed from SQL Server, but when trying to invoke the SP from my ASP page it complains that the SP expects parameter '@RowCount' which was not supplied.
I don't need to supply it when invoking the SP directly, why do I need to supply it from ASP?
I tried defining it as NULL within my SP, but can't seem to get it to accept both the NULL & Output parms.
And while I'm at it, how do I get my ASP page to display this @RowCount value?

Many Thanks.

View Replies ! View Related

SELECT Query Stmt Inside Stored Procedure

Friends, What are the possible usuages of a SELECT query stmt inside a stored procedure ?? How can we process the results of the SELECT query other than for documentation/Reporting purposes(Correct me if i'm wrong in this) ?? can any one throw some lite on this .. Thanks, SqlPgmr

Friends,

What are the possible usuages of a SELECT query stmt inside a stored procedure ??

How can we process the results of the SELECT query other than for documentation/Reporting purposes(Correct me if i'm wrong in this) ??

can any one throw some lite on this ..

Thanks,
SqlPgmr