Visual Studio And Stored Procedures With Uniqueidentifier Not Returning Results
Jul 27, 2007
I have a stored procedure which simply does a SELECT on a table. This table has as a column a uniqueidentifier, which is not part of the PK. If I execute this procedure with the SQL Server tools, it works fine and returns the expected results.
If I execute this SP with Visual Studio, or ASP.NET, no results are returned and the following comes back:
Running [dbo].[spServiceDetail_Get] ( @ServiceDetailID = <DEFAULT>, @VictimWitnessID = <DEFAULT>).
Any ideas what is going wrong? This seems to be a common problem for many of our tables with a uniqueidentifier in them, and is specifically with VS 2005 and ASP.NET ObjectDataSources.
Can I create stored procedures from within Visual Studio 2005, or do I have to do it in SQL Server itself? If it can be done within Visual Studio, can someone offer a simple example or point me to a tutorial? Thanks for your help!
Hi everyone, I dont know if I am posting this to the right area but here goes: I know there is a way you can step into a stored procedure from VS.net, but I dind't know how. I searched google and found this MS KB Article: http://support.microsoft.com/kb/316549 However, on step two, "Under the Servers node in Server Explorer, expand the SQL Server Machine name, expand the SQL Servers Node, ..." The first and only item I have in the tree is my local machine name. If I expand that I do not have a SQL Servers node, all I have are: Crystal Reports Services, Event Logs Management Classes, Management Events, Message Queues, Performance Counters and Services. The instance of SQL Server I am using is on my local machine and I am able to communicate with it. I am using TCP/IP I figured to check the SQL Server Config Manager and see what protocols the db was using. The client protocols are Shared Memory, TCP/IP and Named Pipes (these are enabled) I then looked at the SQL Server 2005 Services. The following are running: SQL Server Integration Services, SQL Server FullText, SQL Server, SQL Server Analysis Services, SQL Server Reporting and SQL Server Agent (This was stopped but I started it) SQL Server Browser is NOT running and I can't start it. I dont know if that has anything to do with it. Am I missing somethign? Thanks, John
Hi.I am under the understanding that having the sql server 2005 db is notenough (like 2000 was) to debug stored procedures.. that I need topurchase (costly) visual studio 2005.Can someone suggest a free or lower cost alternative?sorry to be so cheap.. its the times I think.
I've followed the steps in http://www.sqlteam.com/article/debugging-stored-procedures-in-visual-studio-2005 & in the MSDN for configuring and setting up debugging SQL 2005 stored procedures in VS 2008 (seems to be the same as in VS 2005). Everything works fine until I Step Into the Stored Procedure. Everything says that a yellow arrow will appear on the left and I can start going line by line. I never get the yellow arrow.
If I set a breakpoint, it is automatically disabled. The pop-up warning says, "The breakpoint will not currently be hit. Unable to bind SQL breakpoint at this time. Object containing the breakpoint not loaded." I can't find anything about this message or problem on Microsoft's site or on the web. Any assistance is appreciated.
P.S. I'm running VS 2008 Professional Edition Version 9.0.2.1022.8 RTM
Hi, I used to be able to debug stored procedures via Visual Studio.net 2003. However, this has stopped working. It does not produce an error just simply doesn't work anymore i.e. the breakpoints are by-passed. I have the correct settings in the Debug configuration section. If any-one knows how to rectify this your help would be appreciated. I have thought about re-installing the remote debugging functionality on the server. However, our Visual Studio.net discs are with a developer who is away at present.
Hello, What permissions do I need to set on our new SQL Server 2005 test server so that I can see the stored procedures, views and tables from Visual Studio development environment.
Example, I can see my older SQL Server 7 tables, edit them, write stored procedures, and so on. But while I can see the SQL Server 2005 and its databases, the folder underneath are empty and I cannot right-click to create New stored procedure or table.
My guess is that there is security involved, if so, what do I set on the new server? If I'm wrong, and it's something else entirely, please advise.
I'm having some problems debugging SQL Server stored procedures on a SQL Server 2005 server. I have installed Visual Studio 2005 on a workstation running Windows XP, now I'm trying to debug a ASP.Net web application that has some code that executes the stored procedures on a Windows 2003 Server running SQL Server 2005.
I opened VS2005 ... created a connection to the SQL Server 2005 instance ... open the Stored procedure ... right click the stored procedure name and selected Step into Stored Procedure and the following message is displayed:
Unable to start T-SQL debugging.Could not attach to SQL Server process on 'ServerName'.
Hi all, Suppose I have set a CHECK constraint to a column where Salary field is not permitted to be less than 1000 or greater than 10000. In this situation, when I insert a new record with salary as 10, using a stored procedure from Visual Studio, how will I trap the error from C# Code? Thanks Tomy
Hi,I'm creating a stored procedure that pulls information from 4 tables based on 1 parameter. This should be very straightforward, but for some reason it doesn't work.Given below are the relevant tables: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tbl_Project]( [ProjID] [varchar](300) NOT NULL, [ProjType] [varchar](20) NULL, [ProjectTitle] [varchar](max) NULL, [ProjectDetails] [varchar](max) NULL, [ProjectManagerID] [int] NULL, [RequestedBy] [varchar](max) NULL, [DateRequested] [datetime] NULL, [DueDate] [datetime] NULL, [ProjectStatusID] [int] NULL, CONSTRAINT [PK__tbl_Project__0B91BA14] PRIMARY KEY CLUSTERED ( [ProjID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[tbl_Project] WITH CHECK ADD CONSTRAINT [FK_tbl_Project_tbl_ProjectManager] FOREIGN KEY([ProjectManagerID]) REFERENCES [dbo].[tbl_ProjectManager] ([ProjectManagerID]) GO ALTER TABLE [dbo].[tbl_Project] CHECK CONSTRAINT [FK_tbl_Project_tbl_ProjectManager] GO ALTER TABLE [dbo].[tbl_Project] WITH CHECK ADD CONSTRAINT [FK_tbl_Project_tbl_ProjectStatus] FOREIGN KEY([ProjectStatusID]) REFERENCES [dbo].[tbl_ProjectStatus] ([ProjectStatusID]) GO ALTER TABLE [dbo].[tbl_Project] CHECK CONSTRAINT [FK_tbl_Project_tbl_ProjectStatus]
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tbl_Report]( [ReportName] [varchar](50) NOT NULL, [ProjID] [varchar](300) NULL, [DeptCode] [varchar](50) NULL, [ProjType] [varchar](50) NULL, [ProjectTitle] [varchar](500) NULL, [ProjectDetails] [varchar](3000) NULL, [ProjectManagerID] [int] NULL, [RequestedBy] [varchar](50) NULL, [DateRequested] [datetime] NULL, [DueDate] [datetime] NULL, [ProjectStatusID] [int] NULL, CONSTRAINT [PK_tbl_Report] PRIMARY KEY CLUSTERED ( [ReportName] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[tbl_Report] WITH CHECK ADD CONSTRAINT [FK_tbl_Report_tbl_ProjectManager] FOREIGN KEY([ProjectManagerID]) REFERENCES [dbo].[tbl_ProjectManager] ([ProjectManagerID]) GO ALTER TABLE [dbo].[tbl_Report] CHECK CONSTRAINT [FK_tbl_Report_tbl_ProjectManager] GO ALTER TABLE [dbo].[tbl_Report] WITH CHECK ADD CONSTRAINT [FK_tbl_Report_tbl_ProjectStatus] FOREIGN KEY([ProjectStatusID]) REFERENCES [dbo].[tbl_ProjectStatus] ([ProjectStatusID]) GO ALTER TABLE [dbo].[tbl_Report] CHECK CONSTRAINT [FK_tbl_Report_tbl_ProjectStatus]
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tbl_ProjectStatus]( [ProjectStatusID] [int] IDENTITY(1,1) NOT NULL, [ProjectStatus] [varchar](max) NULL, CONSTRAINT [PK__tbl_ProjectStatu__023D5A04] PRIMARY KEY CLUSTERED ( [ProjectStatusID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tbl_ProjectManager]( [ProjectManagerID] [int] IDENTITY(1,1) NOT NULL, [FName] [varchar](50) NULL, [LName] [varchar](50) NULL, [Inactive] [int] NULL, CONSTRAINT [PK__tbl_ProjectManag__7D78A4E7] PRIMARY KEY CLUSTERED ( [ProjectManagerID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
GO SET ANSI_PADDING OFF
And here is the stored procedure that I wrote (doesn't return results): SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[GetReportQuery] ( @ReportName varchar(100) )
AS
BEGIN
SET NOCOUNT ON
IF @ReportName IS NULL BEGIN RETURN -1 END ELSE BEGIN
DECLARE @DeptCode varchar(50), @ProjID varchar(50) SELECT @DeptCode = DeptCode FROM tbl_Report WHERE ReportName = @ReportName
SET @ProjID = @DeptCode + '-' + '%'
SELECT P.ProjID, P.ProjType, P.ProjectTitle, P.ProjectDetails, M.FName, M.LName, P.DateRequested, P.DueDate, S.ProjectStatus FROM tbl_Project P, tbl_ProjectManager M, tbl_ProjectStatus S WHERE ((P.ProjID = (SELECT ProjID FROM tbl_Report WHERE ((ReportName = @ReportName)))) AND (P.ProjectDetails = (SELECT ProjectDetails FROM tbl_Report WHERE ReportName = @ReportName) OR P.ProjectDetails IS NULL) AND (M.FName = (SELECT FName FROM tbl_ProjectManager WHERE (ProjectManagerID = (SELECT ProjectManagerID FROM tbl_Report WHERE ReportName = @ReportName))) OR M.FName IS NULL) AND (M.LName = (SELECT LName FROM tbl_ProjectManager WHERE (ProjectManagerID = (SELECT ProjectManagerID FROM tbl_Report WHERE ReportName = @ReportName))) OR M.LName IS NULL) AND (P.DateRequested = (SELECT DateRequested FROM tbl_Report WHERE ReportName = @ReportName) OR P.DateRequested IS NULL) AND (P.DueDate = (SELECT DueDate FROM tbl_Report WHERE ReportName = @ReportName) OR P.DueDate IS NULL) AND (S.ProjectStatus = (SELECT ProjectStatusID FROM tbl_Report WHERE ReportName = @ReportName) OR S.ProjectStatus IS NULL) ) END
I have coded a stored procedure to return nearly all of the columns of a single record selected by using a unique key value. The record is in an SQL database, not within an in-memory DataSet. All of the parameters that I wish to have returned to my program are defined as OUTPUT; the two key values are defaulted to INPUT, as there is no need to return them to the calling program. I also have defined the direction of these parameters in the calling SQLDataAdapter function. However, when I run this, the values returned are either the current date for my DateTime parameters, Nothing for my Char parameters or 0's for my integer parameters.
When I try testing the sproc alone, by using the "Step Into Stored Procedure" action in Visual Studio, I get a message in the Debug Output window indicating that parameter @TktClassID was expected and not supplied. This is an OUTPUT parameter, which makes me question why I should be providing any sort of value for it within my VB code. Following are the function definition from my SQLDataAdapter class that calls my sproc, and the sproc itself. I appreciate any help that anyone can provide.
**FUNCTION DEFINITION FROM SQLDataAdapter Class
Public Function Fetch(ByVal ticket As Ticket) As Ticket Dim connbuilder As New System.Data.SqlClient.SqlConnectionStringBuilder connbuilder("Data Source") = "ITS-KCGV7VZSQLEXPRESS" connbuilder("Integrated Security") = "True" connbuilder("Initial Catalog") = "ITSHelpDesk" Using conn As New System.Data.SqlClient.SqlConnection(connbuilder.ConnectionString)
Using comm As New System.Data.SqlClient.SqlCommand("dbo.TicketFetch", conn) conn.Open() comm.CommandType = CommandType.StoredProcedure Dim parm As System.Data.SqlClient.SqlParameter
Hi: I've seen a lot of posts about returning the identity after an insert but for every variation, the return value or output parameter is null. Below is the sp, SQLDataSource and vb code for the return value attempt. Here is the stored procedure: ALTER PROCEDURE dbo.sp_InsertPlayer ( @UserName varchar(64), @Email varchar(128)
) AS INSERT INTO tblPlayer ([UserName], ) VALUES (@UserName, @Email) RETURN SCOPE_IDENTITY()
If the sp is executed in the SQL Express that is included in VS 2005, the return value is the last autogenerated PlayerId. SqlDataSource: <asp:SqlDataSource ID="SqlDataPlayer" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand ... InsertCommand="sp_InsertPlayer" InsertCommandType="StoredProcedure" > ... <InsertParameters> <asp:Parameter Name="RETURN_VALUE" Direction="ReturnValue" Type="Int32" /> <asp:Parameter Name="UserName" Type="String" /> <asp:Parameter Name="Email" Type="String" />
</InsertParameters> </asp:SqlDataSource> code behind vb: SqlDataPlayer.InsertParameters("UserName").DefaultValue = CreateUserWizard1.UserName SqlDataPlayer.InsertParameters("Email").DefaultValue = CreateUserWizard1.Email SqlDataPlayer.Insert() Profile.PlayerId = SqlDataPlayer.InsertParameters("RETURN_VALUE").DefaultValue The DefaultValue is Nothing. Thanks for your help again. arora
Firstly may I say that the sproc I am having problems with and the service that calls it is inherited technical debt from an unsupervised contractor. We are not able to go through a rewriting process at the moment so need to live with this if possible.
Background
We have a service written in c# that is processing packages of xml that contain up to 100 elements of goods consignment data. In amongst that element is an identifier for each consignment. This is nvarchar(22) in our table. I have not observed any IDs that are different in length in the XML element.
The service picks up these packages from MSMQ, extracts the data using XPATH and passes the ID into the SPROC in question. This searches for the ID in one of our tables and returns a bool to the service indicating whether it was found or not. If found then we add a new row to another table. If not found then it ignores and continues processing.
Observations
The service seems to be dealing with a top end of around 10 messages a minute... so a max of about 1000 calls to the SPROC per minute. Multi-threading has been used to process these packages but as I am assured, sprocs are threadsafe. It is completing the calls without issue but intermittently it will return FALSE. For these IDs I am observing that they exist on the table mostly (there are the odd exceptions where they are legitimately missing). e.g Yesterday I was watching the logs and on seeing a message saying that an ID had not been found I checked the database and could see that the ID had been entered a day earlier according to an Entered Timestamp.
So the Sproc...
USE [xxxxxxxxxx] GO
SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
[Code]....
So on occasions (about 0.33% of the time) it is failing to get a bit 1 setting in @bFound after the SELECT TOP(1).
The only suggestions I can make have been...
change @pIdentifier nvarchar(25) to nvarchar(22) Trim any potential blanks from either side of both parts of the identifier comparison Change the SELECT TOP(1) to an EXISTS
The only other thought is the two way parameter direction in the C# for the result OUTPUT. Not sure why he did it that way or what the purpose is.
I have been unable to replicate this using a test app and our test databases. Has observed selects failing to find even though the data is there, like this before?
I have a script with a number of different Left Joins. When I run it in Query Analyzer the Left Join works as a normal join and suppresses some rows. However when the same code is used in a stored procedure the correct results are produced when the stored procedure is executed. Can anyone tell me what is causing this?
FROM PSROLEUSER USR JOIN PSOPRDEFN OPR ON USR.ROLEUSER = OPR.OPRID
LEFT OUTER JOIN PS_PERSONAL_DATA PER ON OPR.EMPLID = PER.EMPLID
LEFT OUTER JOIN PS_EMPLOYEES EMP ON OPR.EMPLID = EMP.EMPLID
LEFT OUTER JOIN PS_JOBCODE_TBL JCD ON JCD.SETID = 'RBLTT' AND EMP.JOBCODE = JCD.JOBCODE AND JCD.EFFDT = (SELECT MAX(EFFDT) FROM PS_JOBCODE_TBL WHERE JCD.SETID = SETID AND JCD.JOBCODE = JOBCODE AND EFFDT <= GETDATE())
WHERE OPR.ACCTLOCK = 0 AND USR.ROLENAME = 'RBL MANAGER'
Hi, i am trying to return two values from SQL 2000 using a single stored procedure. The stored working fine in Query Analyser and returns the two values and two grids in the results window.
My problem is that when i execute the stored procedure using ADO.Net the dataset only has one of the values. e.g TId : 2, where it should read 'TId' : 2, 'ConfigPath': 'C:lah'
Please could anyone shed ligth on this problem?
here the code for the stored procedure:
CREATE PROCEDURE dbo.GetTillInfo ( @TillIdR varchar(50), @Password varchar(50) ) AS
declare @TillId int declare @configpath varchar(150)
IF Exists (SELECT Id FROM Tills WHERE TillRef=@TillId and TillPassword=@Password) BEGIN
set @TillIdR = (SELECT Id FROM Tills WHERE TillRef=@TillId and TillPassword=@Password) select @TillIdR as 'TId'
set @configpath = (SELECT configpath from customer,tills where tills.customerid = customer.id and tills.id = @login) select @configpath as 'ConfigPath' END ELSE BEGIN set @TillIdR = 0 select @TillIdR as 'TId' set @configpath ='' select @configpath as 'ConfigPath' END GO
HiIs it possible to use Visual Source Safe with SQL Server inorder to keep track of all modifications done to stored procedures?What are the pros and cons ?Thanks, Eugene
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?
ALTER PROCEDURE dbo.usp_Create_Fact_Job (@startDate date, @endDate date) AS /*--Debug--*/ --DECLARE @startDate date --DECLARE @endDate date
--SET @startDate = '01 APR 2014' --SET @endDate = '02 APR 2014' ; /*-- end of Debug*/ WITH CTE_one AS ( blah blah blah)
SELECT a whole bunch of fields from the joined tables and CTEs...When I run the code inside the stored procedure by Declaring and setting the start and enddates manually the code runs in 4 minutes (missing some indexes ).When I call the stored procedure with the ExEC
It never returns a results set but doesn't error out either. I have left it for 40 minutes and still no joy.The sproc is reasonably complicated; 6 CTEs to find the most recent version of records and some 2 joins to parent tables (parent and grandparent), 3 joins to child tables (child, grandchild and great grandchild) and 3 joins to lookup views each of which self references a table to filter for last version of a record.
You know this sample ODS dll project: $80oolsdevtoolssamplesodsxp_hello
I need to find a template like this in Visual Basic.
On this site I read through the xp_Encrypt project which was developed in VB. I did not see source code or a downloadable project file.
My searching on the internet hasn't yielded any practicle results, all examples are either how to use an extended stored procedure or are a MSVC++ project.
Anyone find VB related resources, anywhere? Books, TV, magazines????
Hello,I want to debug a Stored Procedure in the VIsual Studio. Actually I managed to do that, but only from Step into SP and Execute. I want to put a breakpoint in the procedure and when it is hit to stop, but if I Run(With Debug) my Site it doesn't stop at the breakpoint in the SP. I put a mark in the project options to debug SQL. What can be wrong?
Hi ,I am using Visual studio 2005 with sql server 2005. I want to debug my stored procedure, which is situated on the server on the network(accessible through network share). I followed the following URL: http://aspnet.4guysfromrolla.com/articles/051607-1.aspxI have used Direct database debugging : When I right click my stored procedure and click 'step into stored procedure', I get the following error: "Unable to start T-SQL debugging. could not attach to SQL server process on 'sql_server_name'. The remote procedure call failed and did not execute" I am using windows authentication to login to sql server Any help?
I need to keep track of the number of hits on a particular page. Im using a stored Procedure
What I want to do is get the number of hits and increment it by one :)
ie: Sub Procedure should be like below
SELECT noOfHits WHERE pageName = 'bla bla'
noOfHits = noOfHits + 1 etc.
Also, some of the pages will be added and deleted all the time, so before I increment the noOfHits variable I need to check that the pageName 'bla bla' exists. AND if it doesnt I need to create a pageName called 'bla bla'
What I need to do in essence is:
1. Check that a particular row exists. if it doesnt create it. 2. Increment a value (by one) to a column in this particular row.
Phew. Hope you got that. Any ideas much appreciated,
Has anyone had a problem where the stored procedure does not return the same results as compared to running the same SQL in ISQL? This happens when rows are added to a table that's in a join and the SP doesn't return those rows. Once it starts happening, it will not work consistently, but it doesn't always happen when new rows are added to a table. It has happened to many stored procedures and with many different tables. sp_recompile won't fix the problem. Only dropping and re-adding the SP will fix the problem with that stored procedure. We are using SQL 6.5.
I have created multiple stored procedures that search different tables for similiar information.
Is it possible to have one main stored procedure that calls and executes each of these individual stored procedures and then use the UNION keyword to combine the results?
For example
Code:
CREATE PROCEDURE [dbo].[Return_Detail]
@IDVarChar(200)
AS
--Get the 1st Detail EXECReturn_1st_Detail @ID = @ID
UNION
--Get the 2nd Detail EXECReturn_2nd_Detail @ID = @ID GO
Hi. Does anyone know how to display the results if i execute "xp_fixeddrives, xp_availablemedia and xp_subdirs" commands with VC++ 6.0? I can't obtained the results using Recordset class. Can someone help me? Thank you.
In my SPs, I commonly have a situation, where a SELECT statement gets a single scalar value (e.g. SELECT Name FROM Employee WHERE id=@id) from a table.
Because the result is still a relation, I cannot process it directly or assign the result to a variable (like set @name = SELECT Name FROM Employee WHERE id=@id)
So, how can I process the results of the statement in this case.
In some other cases, the result is actually a relation. And I want to iterate over all rows, processing each row's columns. (I know this smells of ADO.NET, but how can I help it if I am coming from that background)...
The point is I want to do all this in T-Sql on server side!!!
i'm trying to create my first stored procedure. i believe i've done everything correctly, the file shows up in the projects directory with the .sql extension, but the proc doesnt appear in object explorer under my database name/programmability/stored procedures. when i execute the query, i get "Could not find stored procedure 'IsEmployeeInTable'.". i'm running on my laptop using asp.net development server. thanks. matt
