Using Statements With Stored Procedures In SQLJDBC 1.2
Dec 4, 2007
Hi,
I am doing some research on sqljdbc 1.2. I have run into a few problems with the jdbc driver. I think it would be so much easier just to ask the expert here.
1. when to use CallableStatment, PreparedStatement, Statement for stored proc and why?
http://msdn2.microsoft.com/en-us/library/ms378046.aspx
talks about using Statements with Stored Procedures.
It says for stored Proc with no param, use Statement; for stored proc with only input param, use PreparedStatement; and for stored proc with output param, return status or update count, use CallableStatement.
Is there any special considerations to select which one to use, is there any performance differences? I assume we can always use CallableStatement for stored proc.
2. mutliple statements on one Connection
The JDBC spec allows multiple Statement on one Connection with no need to close the previous Statment. When Connection.close() is called, all the Statements and ResultSets will be closed too.
I tried with regular stored proc, it is fine of have a CallableStatement, then a Statement, without close the CallableStatment, all the Statements are executed with no error.
However, when I change the stored proc, using explicit transaction, i.e BEGIN TRANSACTION, and COMMIT TRANSACTION, the Statement failed and throw an error:
com.microsoft.sqlserver.jdbc.SQLServerException: Server failed to resume the transaction, desc: 3500000001.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(Unknown Source)
When I add a CallableStatement.close() before the create the second Statment, then it worked. Is this a bug or something special needs to be set for stored proc with Transaction?
3. Statement.getGeneratedKeys
Under the hood, is it making another round trip to get the generatedkeys, or when the Statement created with Statement.RETURN_GENERATED_KEYS is excuted, the identity field is already retured with the Statement?
This method call works with regular insert Statement, but does not work for PreparedStatement calling a stored proc. Is this a bug?
I have used SQL for donkey's years, but never really used Stored Procedures, well, I am starting to migrate functions from a front end ASP.Net system, and utilising the stored procedures.
I have a problem with something I thought would be really basic, i am trying to use IF THEN statements within the stored procedure to change the WHERE elements based on the parameters passed to it.
Here is the script (copied from the modification screen)
USE [QP] GO /****** Object: StoredProcedure [dbo].[USERS_LIST] Script Date: 02/21/2008 21:50:31 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER Procedure [dbo].[USERS_LIST] @SearchVarChar(100) As SELECT TOP 100 PERCENT dbo.Members.EntryID, dbo.Members.EntryDate, dbo.Members.Username, dbo.Members.Forename, dbo.Members.Surname, dbo.Members.Gender, dbo.Members.DateofBirth, dbo.Members.LastAction, dbo.Members.AdminUser, dbo.ActiveMember_Status.Status, dbo.ActiveMember_Mobile.Value AS Mobile, dbo.ActiveMember_Email.Value AS Email, dbo.ActiveMember_Location.Location1, dbo.ActiveMember_Location.Location2, dbo.ActiveMember_Location.Location3, dbo.ActiveMember_Location.Location4, dbo.F_AGE_IN_YEARS(dbo.members.dateofbirth, GetDate()) As Age FROM dbo.Members INNER JOIN dbo.ActiveMember_Status ON dbo.Members.EntryID = dbo.ActiveMember_Status.UserID LEFT OUTER JOIN dbo.ActiveMember_Location ON dbo.Members.EntryID = dbo.ActiveMember_Location.UserID LEFT OUTER JOIN dbo.ActiveMember_Email ON dbo.Members.EntryID = dbo.ActiveMember_Email.UserID LEFT OUTER JOIN dbo.ActiveMember_Mobile ON dbo.Members.EntryID = dbo.ActiveMember_Mobile.UserID IF (@Search='Dowle') WHERE (dbo.Members.Username = @Search) OR (dbo.Members.Forename = @Search) OR (dbo.Members.Surname = @Search) OR (dbo.ActiveMember_Mobile.Value = @Search) OR (dbo.ActiveMember_Email.Value = @Search) OR (dbo.ActiveMember_Location.Location1 = @Search) OR (dbo.ActiveMember_Location.Location2 = @Search) OR (dbo.ActiveMember_Location.Location3 = @Search) OR (dbo.ActiveMember_Location.Location4 = @Search) END IF
ORDER BY dbo.Members.Username
The bit I am trying to do above isn't real, but the same error appears every which way I try.
Msg 156, Level 15, State 1, Procedure USERS_LIST, Line 14 Incorrect syntax near the keyword 'WHERE'.
Hi,We're having a big discussion with a customer about where to store the SQL and DML statements. (We're talking about SQL Server 2000)We're convinced that having all statements in the code (data access layer) is a good manner, because all logic is in the "same place" and it's easier to debug. Also you can only have more problems in the deployment if you use the stored procedures. The customer says they want everything in seperate stored procedures because "they always did it that way". What i mean by using seperate stored procedures is:- Creating a stored procedure for each DML operation and for each table (Insert, update or delete)- It should accept a parameter for each column of the table you want to manipulate (delete statement: id only)- The body contains a DML statement that uses the parameters- In code you use the name of the stored procedure instead of the statement, and the parameters remain... (we are using microsoft's enterprise library for data access btw)For select statements they think our approach is best...I know stored procedures are compiled and thus should be faster, but I guess that is not a good argument as it is a for an ASP.NET application and you would not notice any difference in terms of speed anyway. We are not anti-stored-procedures, eg for large operations on a lot of records they probably will be a lot better.Anyone knows what other pro's are related to stored procedures? Or to our way? Please tell me what you think...Thanks
Is it true that replacing embedded sql statements (that insert/update and fetch data from sql databases) by stored procedures improves overall respones time and SQL performance. We have tons of embedded SQL statements in our Visual Basic modules and are debating whether replacing them by SQL Stored procedures will really be worht the effort.
Any insights on this?
Thanks in advance. If possible, please reply to my e-mail.Thanks.
trying to get to the bottom of this for some time...... eventually tobe used with asp.heres the problemthe following rather complex SQL statement works fine via queryanalyser:SELECT TOP 100 tbl_LevelDetail.nvchLevelName AS DataLevelName,MAX(CASE tintDataFieldId WHEN '1' THEN CAST(nvchData AS int) ELSE 0END) AS 'Pos',MAX(CASE tintDataFieldId WHEN '2' THEN CAST(nvchData AS char) ELSE '' END) AS 'AreaName',MAX(CASE tintDataFieldId WHEN '3' THEN CAST(nvchData AS char) ELSE '' END) AS 'BDGName',MAX(CASE tintDataFieldId WHEN '4' THEN CAST(nvchData AS char) else '' END) AS 'Performance',MAX(CASE tintDataFieldId WHEN '5' THEN CAST(nvchData AS int) ELSE 0END) AS 'Qualifier'FROM tbl_Data, tbl_Levels, tbl_LevelDetail, tbl_LevelDetail AStbl_LevelDetail_ReportWHERE tbl_Data.nvchIncentiveId = 'MPW' AND tbl_Data.nvchPeriodId ='W27P'AND tbl_Levels.nvchIncentiveId = 'MPW' ANDtbl_LevelDetail.nvchIncentiveId = 'MPW'AND tbl_LevelDetail_Report.nvchIncentiveId = 'MPW' ANDtbl_Data.nvchDataLevelId = tbl_Levels.nvchDataLevelIdAND tbl_Levels.nvchDataLevelId = tbl_LevelDetail.nvchLevelIdAND tbl_Levels.nvchReportingLevelId =tbl_LevelDetail_Report.nvchLevelIdAND tbl_LevelDetail.nvchLevelTypeId = 2AND tbl_LevelDetail_Report.nvchLevelTypeId = 1AND tbl_Levels.nvchReportingLevelId IN ('a')GROUP BY tbl_Levels.nvchReportingLevelId, tbl_Levels.nvchDataLevelId,tbl_LevelDetail.nvchLevelName, tbl_LevelDetail_Report.nvchLevelNameORDER BY Pos, DataLevelNamereturns rows ok no problembut when trying to convert to a stored procedure i dont get anyresults:CREATE PROCEDURE usp_incmpwfilter_rs(@strPeriodID varchar ,@intLevelDetailID varchar,@intLevelReportID varchar,@strFilters varchar)ASset nocount onSELECT TOP 100 tbl_LevelDetail.nvchLevelName AS DataLevelName,MAX(CASE tintDataFieldId WHEN '1' THEN CAST(nvchData AS int) ELSE 0END) AS 'Pos',MAX(CASE tintDataFieldId WHEN '2' THEN CAST(nvchData AS char) ELSE '' END) AS 'AreaName',MAX(CASE tintDataFieldId WHEN '3' THEN CAST(nvchData AS char) ELSE '' END) AS 'BDGName',MAX(CASE tintDataFieldId WHEN '4' THEN CAST(nvchData AS char) else '' END) AS 'Performance',MAX(CASE tintDataFieldId WHEN '5' THEN CAST(nvchData AS int) ELSE 0END) AS 'Qualifier'FROM tbl_Data, tbl_Levels, tbl_LevelDetail, tbl_LevelDetail AStbl_LevelDetail_ReportWHERE tbl_Data.nvchIncentiveId = 'MPW' AND tbl_Data.nvchPeriodId =@strPeriodIDAND tbl_Levels.nvchIncentiveId = 'MPW' ANDtbl_LevelDetail.nvchIncentiveId = 'MPW'AND tbl_LevelDetail_Report.nvchIncentiveId = 'MPW' ANDtbl_Data.nvchDataLevelId = tbl_Levels.nvchDataLevelIdAND tbl_Levels.nvchDataLevelId = tbl_LevelDetail.nvchLevelIdAND tbl_Levels.nvchReportingLevelId =tbl_LevelDetail_Report.nvchLevelIdAND tbl_LevelDetail.nvchLevelTypeId = @intLevelDetailIDAND tbl_LevelDetail_Report.nvchLevelTypeId = @intLevelReportIDAND tbl_Levels.nvchReportingLevelId IN (@strFilters )GROUP BY tbl_Levels.nvchReportingLevelId, tbl_Levels.nvchDataLevelId,tbl_LevelDetail.nvchLevelName, tbl_LevelDetail_Report.nvchLevelNameORDER BY Pos, DataLevelNamethen call it by SQL statement:EXEC usp_incmpwfilter_rs 'W27P',2,1,'a'Returns no rows. This is the initial problem. Also there will beanother issue if i can get the above to work: the @strFilters cancontain multiple data, ie 'a','k'this works fine in the 1st sql statement ie: ANDtbl_Levels.nvchReportingLevelId IN ('a','k') but I dont know how topass as a parameter to the stored procedure. I cannot create temporarytables.i had not created the intial SQL statement, i am just trying toconvert it to a stored procedure which accepts thos parameters. thishas been a real headache for me, any help as always appreciatedgreatly.
I have nested a Stored Procedure within a stored procedure. The nested stored procedure ends in a select statement. What I'd like to do is either capture the results of the select statement (it will be 1 row with 3 columns, I only need the contents of first column), or suppress the select statement from displaying in the final results of the Stored Procedure it is nested in.
Hai, I just waana know whether we can use "FOR statement" in Stored Procedures. If yes, can you describe it with some examples of how they are used in stored procedures ?
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!!!
Hi, all I'm using Sql server 2000 I want to make select statement dynamically and return table using function. in sp, I've done this but, in function I don't know how to do so. (I have to create as function since our existing API..)
Following is my tials... 1. alter Function fnTest ( @fromTime datetime, @toTime datetime) RETURNS Table AS
RETURN Exec spTest @from, @to GO
Yes, it give syntax error..
2. So, I found the following
From Sql Server Books Online, Remark section of CREATE FUNCTION page of Transact-SQL Reference , it says following..
"The following statements are allowed in the body of a multi-statement function. Statements not in this list are not allowed in the body of a function: " ..... * EXECUTE statements calling an extended stored procedures.
So, I tried.
alter Function fnTest ( @fromTime datetime, @toTime datetime) RETURNS Table AS
RETURN Exec master..xp_msver GO
It doesn't work... syntax err...
Here I have quick question.. How to execute statements calling an extended stored procedures. any examples?
Now, I'm stuck.. how can I create dynamic select statement using function?
I have few print statements in a stored procedure that gets called from the SSIS package. How do I make sure that these are captured in the SSIS log meaning how do I get them to be displayed in the Package Explorer window when running from the Business Intelligence Studio IDE.
I need to list out all the procedures which have select queries and does not use NOLOCK hint. Please note that there are more than 2000 sps and some sps have single select some have multiple, some does not have select queries at all. I need to find out only the ones which does not use NOLOCK hint in a select query.
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Look into sqljdbc.jar: it contains inside a zero-length file named sqljdbc.jar, and the file named sqljdbc.jar.old, with a size of about 280kb. This actually doubles the size of the jar. Was it done on purpose (e.g., old jar is called in some way), or just a mistake of packaging?
Hello, i used sqljdbc (driver jdbc microsoft 1.1) Erreur code sql server 3971 Connction status 0001 com.microsoft.sqlserver.jdbc.SQLServerException: Le serveur n'a pas pu reprendre la transaction, desc. : 9f00000040. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source) at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerStatement.doCloseServerCursor(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerStatement$CloseServerCursorRequest.execute(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerStatement.closeServerCursor(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.close(Unknown Source)
Hello; I failed to connect with my database, it give me the massage as following;
Cannot load the SQL driver. Include it in your classpath
Could anyone tell me why? Thanks
And i am using the Microsoft SQL server 2000, Java 2 runtime enviroment and Microsoft SQL Server 2005 JDBC Driver now.
In the user varible, i set a path for the java run time as "c:Program FilesJavaj2re1.4.2javaws" and CLASSPATH =.;C:Program FilesMicrosoft SQL Server 2005 JDBC Driversqljdbc_1.1enusqljdbc.jar for the JDBC Driver.
and i also tried 1) -classpath =.;"C:Program FilesMicrosoft SQL Server 2005 JDBC Driversqljdbc_1.1enusqljdbc.jar"
2)i moved the file of the sqljdbc.jar to a path as c: emp
//information of our connection //the url of the database: protocol:subprotocol:subname:computer_name:port:database_name String strUrl = "jdbc:sqlserver://localhost;user=xx;password=xxxx";
//user name and password String strUser = "xx"; String strPass = "xxx";
//try to load the driver try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); } catch (ClassNotFoundException e) { System.out.println( "Cannot load the SQL driver. Include it in your classpath.");
System.exit( -1); }
//a null reference to a Connection object c = null;
try { //open a connection to the database c = DriverManager.getConnection( strUrl);
} catch (SQLException e) { System.out.println("Cannot connect to the database. Here is the error:"); e.printStackTrace(); System.exit( -1); }
//create a statement object to execute sql statements
displayInformation(); }
By the way, how i can delete a system variable? i set the classpath for the sqljdbc in system variable section, but i want to delete it, but when i click the delete button, it does not has reaction. Do you known i i can solve this problem? Thanks
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
We are porting a database from DB2 to SQL-Server 2005. We use WAS 5.1. We have problems with insert staments followed by an SELECT @@IDENTITY. The sql error is that no result set is returned. The syntax is "<insert statment>;SELECT @@IDENTITY. We have no problems with this in the 1.1 version (there we have the known "read date" error).
The following exception is thrown with sqljdbc.jar (not with jtds0.9.jar)
com.microsoft.sqlserver.jdbc.SQLServerException: New request is not allowed to start because it should come with valid transaction descriptor. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source) at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(Unknown Source) at com.microsoft.sqlserver.jdbc.TDSParser.parse(Unknown Source) at com.microsoft.sqlserver.jdbc.TDSParser.parse(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection$1ConnectionCommand.doExecute(Unknown Source) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectionCommand(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.rollback(Unknown Source) at org.apache.commons.dbcp.DelegatingConnection.rollback(DelegatingConnection.java:265) at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.rollback(PoolingDataSource.java:288)
While using the query :
if not exists ( select 1 from sysindexes where id = object_id('aaa') and name = 'aaa_pk')create unique nonclustered index aaa_pkon aaa(id)using Statement.executeBatch()at conection.commit()
Sample Code
conn = getConnection();
// create the statement and execute the query Statement stmt = null; try { conn.setAutoCommit(false); stmt = conn.createStatement(); for ( String sql : sqlList ) { stmt.addBatch( sql ); }
results = stmt.executeBatch(); stmt.clearBatch(); conn.commit(); //throws the exception } catch ( SQLException e ) { try { conn.rollback(); } catch ( SQLException e1 ) { throw new DataSourceException( e1 ); } throw new DataSourceException( "Error executing sql: %1", e, sqlList.toString() ); }
When performing a failover the DriverManager.getConnection method never returns from it's call. I used a modified version of the test program included in the sqljdbc help file (below). If the principle server is server2 and a failover is performed, the program works just fine. If the priciple server is server1 and a failover is performed, the hang occurs. The sql servers are not on a domain, are in the same workgroup, have certificates setup between them. Sqljdbc version used is 1.1.1501.101. I rolled back to version 1.1.1320.0 and the DriverManager.getConnection exceptioned. Not sure if this is a setup issue or a common problem. Anyone have any suggestions or seeing similar issues?
Thanks
- tc
Version 1.1.1501.101 - System hangs on DriverManager.getConnection method. Version 1.1.1320.0 - Exception: java.nio.channels.UnresolvedAddressException at sun.nio.ch.Net.checkAddress(Net.java:30) at sun.nio.ch.SocketChannelImpl.connect(SocketChannelImpl.java:451) at java.nio.channels.SocketChannel.open(SocketChannel.java:146) at com.microsoft.sqlserver.jdbc.DBComms.<init>(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.loginWithFailover(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source) at java.sql.DriverManager.getConnection(DriverManager.java:525) at java.sql.DriverManager.getConnection(DriverManager.java:193) at testconnection.Main.Execute(Main.java:115) at testconnection.Main.main(Main.java:35)
private static void Execute(int iteration) { // Create a variable for the connection string. String connectionUrl = "jdbc:sqlserver://server1;" + "databaseName=TestDb;integratedSecurity=true;" + "failoverPartner=server2";
// Declare the JDBC objects. Connection con = null; Statement stmt = null;
try { System.out.println("(" + iteration + ") Connection Test"); // Establish the connection to the principal server. Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); System.out.println(" Get Connection."); con = DriverManager.getConnection(connectionUrl); System.out.println(" Connected to the principal server.");
// Create and execute an SQL statement that inserts some data. stmt = con.createStatement(); ResultSet rs = stmt.executeQuery("SELECT * FROM TestTable"); System.out.println(" SUCCESS");
while(rs.next()) { System.out.println(" Record: " + rs.getString("Field1")); } } // Handle any errors that may have occurred. catch (SQLException se) { try { // The connection to the principal server failed, // try the mirror server which may now be the new // principal server. System.out.println(" Connection to principal server failed, " + "trying the mirror server."); con = DriverManager.getConnection(connectionUrl); System.out.println(" Connected to the new principal server."); stmt = con.createStatement(); ResultSet rs = stmt.executeQuery("SELECT * FROM TestTable"); System.out.println(" SUCCESS");
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)
'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.
Hi all,I have 2 independent stored procedures(I cannot join them because ofthe group condition are different however they have the same cust id)but one for reading the summary and other showing the details of thesummary. Now I want to run these two stored procedures in order toget the both return results for generating a .Net crytral report. CanI get the results form both stored procedures and how can I do that.Please help me. Thanks a lot.Hello
Hello all, I am having a hard time getting this to work. I am trying to build the where statement dynamically but it is a string and needs quotes. But the problem is the quotes are the problem. Are there any escape characters? or is there a way to make this happen??? Integers are easy keys because they do not need quotes.
SET @wheretmp = 'WHERE SourceIPID = BB901625-5E89-45D4-BD20-25730365A9DA'
I have a small problem writing a stored procedure in a SQL Server 2000 database.
I would like to generate som part of the SQL inside this stored procedure that is used in an IN expression of my WHERE clause. There is no problem for me to generate a string containing my expression, the problem is that SQL-Server don´t generate a resulting SQL-statement.
Example:
CREATE PROCEDURE spDynStatement AS
DECLARE @sPartOfSQLStatement NVARCHAR(100)
-- Some T-SQL that generates the dynamic part of the SQL-statement -- . -- . -- .
-- As substitute I insert the string expression SET @sPartOfSQLStatement = '''1''' + ', ' + '''1.5'''
I believe we can you multiple statements in stored procedures?
Suppose I have a stored procedure and I pass parameters to this SP. What I am aiming for is to pass some values to the stored procedure, use a select statement to retrieve some values, then have two update statements as below. Updating the same table but with opposite values, both passed as a parameter and retrived, as given below:
Hi!I got 2 stored procedure, proc1 executes proc2,proc2 does some updates and inserts on different tables ...proc1:ALTER PROCEDUREASexecute proc2SELECT * FROM tblFoo______________________my problem is, that when executing proc1, I receive the message:"THE SP executed successfully, but did not return records!"But I need the resultset from "SELECT * FROM tblFoo" that is executedat the end of proc1.I'm not sure, but I think that I solved a similira problem with "setnocount on", I put it into both SP, but it's still the same ... noresultset ...How can I display "SELECT * FROM tblFoo" within a SP, where SQLstatements are executed before?!Thank you!
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out? SELECT TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
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?
I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.
How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?
I have a SP that has the correct syntax. However when I run my web-app it gives me this error: "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. " The procedure takes in three parameters and retrieves 23 values from the DB to display on my form. Any ideas?
