Stored Procedure Where Statements With Nvarchar

Nov 30, 2000

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'

Select * from tbl_All_Source @wheretmp

View 1 Replies


ADVERTISEMENT

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

Mar 3, 2008

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 11 Replies View Related

How To Execute An A NVARCHAR Variable In A Stored Procedure ?

Sep 1, 2007

Hi fellows
I have a query in a NVARCHAR  variable I want to execute it , It supposed to create a "Table  Variable"  ,it is like this :"DECLARE @ProductTotals TABLE
([SideTab]NvarChar(250),[Jun  1 2007 12:00AM] int,[Jun  2 2007 12:00AM] int,[Jun  3 2007 12:00AM] int,[Jun  4 2007 12:00AM] int,[Jun  5 2007 12:00AM] int,[Jun  6 2007 12:00AM] int,[Jun  7 2007 12:00AM] int,[Jun  8 2007 12:00AM] int,[Jun  9 2007 12:00AM] int,[Jun 10 2007 12:00AM] int,[Jun 11 2007 12:00AM] int,[Jun 12 2007 12:00AM] int,[Jun 13 2007 12:00AM] int,[Jun 14 2007 12:00AM] int,[Jun 15 2007 12:00AM] int,[Jun 16 2007 12:00AM] int,[Jun 17 2007 12:00AM] int,[Jun 18 2007 12:00AM] int,[Jun 19 2007 12:00AM] int,[Jun 20 2007 12:00AM] int,[Jun 21 2007 12:00AM] int,[Jun 22 2007 12:00AM] int,[Jun 23 2007 12:00AM] int,[Jun 24 2007 12:00AM] int)" 
And  it is stored in a variable  say  @nvcVar. 
I want to execute it
I did :
EXECUTE     @nvcVar
But it gives me this error :Msg 203, Level 16, State 2, Procedure proc_Report_DailyReport_Karkard, Line 60The name 'DECLARE @ProductTotals TABLE ([SideTab]NvarChar(250),[Jun  1 2007 12:00AM] int,[Jun  2 2007 12:00AM] int,[Jun  3 2007 12:00AM] int,[Jun  4 2007 12:00AM] int,[Jun  5 2007 12:00AM] int,[Jun  6 2007 12:00AM] int,[Jun  7 2007 12:00AM] int,[Jun  8 2007 12:00AM] int,[Jun  9 2007 12:00AM] int,[Jun 10 2007 12:00AM] int,[Jun 11 2007 12:00AM] int,[Jun 12 2007 12:00AM] int,[Jun 13 2007 12:00AM] int,[Jun 14 2007 12:00AM] int,[Jun 15 2007 12:00AM] int,[Jun 16 2007 12:00AM] int,[Jun 17 2007 12:00AM] int,[Jun 18 2007 12:00AM] int,[Jun 19 2007 12:00AM] int,[Jun 20 2007 12:00AM] int,[Jun 21 2007 12:00AM] int,[Jun 22 2007 12:00AM] int,[Jun 23 2007 12:00A' is not a valid identifier.  
What is the problem ?!
 Thank you in advance ,
 

View 8 Replies View Related

Can Stored Procedure Run More Than 1 Sql Statements

Jul 20, 2005

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

View 3 Replies View Related

Dynamic SQL Statements In A Stored Procedure

Mar 18, 2004

Hi

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'''

-- SELECT @sPartOfSQLStatement results in: '1' , '1.5'

SELECT * FROM BBNOrganization WHERE OrgStructureID IN( @sPartOfSQLStatement ) -- does not work

SELECT * FROM BBNOrganization WHERE OrgStructureID IN( '1', '1.5' ) -- works!!!
GO

Thankfull for ideas on how to solve my problem,

Peter

View 1 Replies View Related

Multiple Statements In Stored Procedure

Apr 29, 2008

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:

CREATE sp_temp_proc
@order_id int,
@order_position int,
@temp_order_id OUTPUT
@temp_order_position OUTPUT,

SELECT order_id AS temp_order_id
FROM <TABLE> WHERE order_position < @order_position

@temp_order_id = temp_order_id


UPDATE <TABLE> SET order_position = @order_position WHERE order_id =
@temp_order_id

UPDATE <TABLE> SET order_position = @temp_order_position WHERE order_id = @order_id

View 2 Replies View Related

Multiple SQL Statements In A Stored Procedure

Jul 23, 2005

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!

View 1 Replies View Related

T-SQL (SS2K8) :: Store Result Of Stored Procedure Into XML / Nvarchar (max) Variable

Feb 16, 2012

I have a stored procedure that returns XML using FOR XML Explicit. I need to use the output of this procedure in another procedure, and modify the xml output before it is saved somewhere.

Say StoredProc1 is the one returning xml output and StoredProc2 needs to consume the output of StoredProc1

I declared a nvarchar(max) variable and trying to saved the result of StoredProc1

Declare @xml nvarchar(max)
EXEC @xml = StoredProc1 @Id

This doesn't work as expected as @xml doesn't get any value assigned or rather I would say

EXEC @xml = StoredProc1 @Id

outputs the entire xml whereas it should just save the xml in a variable.

View 7 Replies View Related

Syntax To Pass A Parameter Of Type Nvarchar(max) To Stored Procedure

Dec 26, 2007

I have a stored procedure that contains a paramteter of type nvarchar(max). What is the syntax to pass this parameter to the sp from a VB.Net application. Specifically, I need to know what to put in the 3rd parameter below:

cmd.Parameters.Add("@Name", SqlDbType.NVarChar, , Name)

View 1 Replies View Related

Stored Procedure With Multiple Update Statements

Jan 31, 2008

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?
 

View 4 Replies View Related

Multiple Select Statements + Stored Procedure

Aug 9, 2007



Hi all,

I have 2 select statements in my Stored Proc.

I want to display the results of each query in my DataGridView.

However, only the data of the last select query is returned.

Why is this?

Thanks.

View 7 Replies View Related

DB Engine :: Profiling Statements Within A Stored Procedure?

Apr 22, 2015

I'm profiling a specific procedure on a database, I have the start and end times captured but within the same trace I would like to capture all the individual sql statements.

So, given this simple procedureĀ 

ALTER PROCEDURE [dbo].[usp_b]
AS
BEGIN
SET NOCOUNT ON;
SELECT @@VERSION;
SELECT GETDATE();
END

In the trace I would like to see the two SELECTs, however all I get is thisĀ 

I have the following events selected in the trace.

View 13 Replies View Related

Stored Procedure With Multiple Select Statements And SQLDataSource

May 24, 2007

I have created a stored procedure with multiple select statements using MSSQL 2000. When I connect to this using a SQLDataSource it only seems to get the first SELECT. I need to access the multiple tables returned by the stoped procedure. Can anyone point me in the dirrection of how to do this.ThanksClearz 

View 3 Replies View Related

Returning And Reading Multiple Select Statements From One Stored Procedure

Dec 3, 2006

Hey Guys. Iā€™m having a little trouble and was wondering if you could help me out. Iā€™m trying to create a custom paging control, so I create a stored procedure that returns the appropriate records as well as the total amount of records. And that works fine. What Iā€™m having problems with is reading the data from the second select statement within the code. Anyone have any idea on how to do this? Also.. how can I check how many tables were returned?
Here's my code. I'm trying to keep it very generic so I can send it any sql statement:public DataTable connect(string sql)
{
DataTable dt = new DataTable();

SqlConnection SqlCon = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["MyDB"].ToString());
SqlDataAdapter SqlCmd = new SqlDataAdapter(sql, SqlCon);
System.Data.DataSet ds = new System.Data.DataSet();
SqlCmd.Fill(ds);

dt = ds.Tables[0];

//Here's where I don't know how to access the second select statement

return dt;
}  Here's my stored procedure:
 ALTER PROCEDURE dbo.MyStoredProcedure
(
@Page int,
@AmountPerPage int,
@TotalRecords int output
)

AS


WITH MyTable AS
(

Select *, ROW_NUMBER() OVER(ORDER BY ID Desc) as RowNum
From Table
where Deleted <> 1
)


select * from MyTable
WHERE RowNum > (((@Page-1)*@AmountPerPage)) and RowNum < ((@Page*@AmountPerPage)+1);

Select @TotalRecords = COUNT(*)
from Table
where Deleted <> 1
RETURN

Thanks

View 3 Replies View Related

Transact SQL :: GO Statements To Execute Large Stored Procedure In Batches

Jun 19, 2015

I want to include GO statements to execute a large stored procedure in batches, how can I do that?

View 9 Replies View Related

Problem About Pass A Big String (over 8000 Characters) To A Variable Nvarchar(max) In Stored Procedure In SQL 2005!

Dec 19, 2005

Problem about pass a big string (over 8000 characters) to a variable nvarchar(max) in stored procedure in SQL 2005!
I know that SQL 2005 define a new field nvarchar(max) which can stored 2G size string.
I have made a stored procedure Hellocw_ImportBookmark, but when I pass a big string  to  @Insertcontent , the stored procedure can't be launch! why?
create procedure Hellocw_ImportBookmark  @userId         varchar(80),  @FolderId       varchar(80),  @Insertcontent  nvarchar(max)
as  declare @contentsql nvarchar(max);  set @contentsql=N'update cw_bookmark set Bookmark.modify(''declare namespace x="http://www.hellocw.com/onlinebookmark"; insert '+                    @Insertcontent+' as last into (//x:Folder[@Id="'+@FolderId+'"])[1]'')  where userId='''+@userID+'''';  exec sp_executesql @contentsql;

View 2 Replies View Related

Problem About Pass A Big String (over 8000 Characters) To A Variable Nvarchar(max) In Stored Procedure In SQL 2005!

Dec 19, 2005

Problem about pass a big string (over 8000 characters) to a variable nvarchar(max) in stored procedure in SQL 2005!

I know that SQL 2005 define a new field nvarchar(max) which can stored 2G size string.

I have made a stored procedure Hellocw_ImportBookmark, but when I pass a big string  to  @Insertcontent , the stored procedure can't be launch! why?

 

 

 

 

----------------------13-------------------------------------
create procedure Hellocw_ImportBookmark
  @userId         varchar(80),
  @FolderId       varchar(80),
  @Insertcontent  nvarchar(max)

as
  declare @contentsql nvarchar(max);
  set @contentsql=N'update cw_bookmark set Bookmark.modify(''declare namespace x="http://www.hellocw.com/onlinebookmark"; insert '+
                    @Insertcontent+' as last into (//x:Folder[@Id="'+@FolderId+'"])[1]'')  where userId='''+@userID+'''';
  exec sp_executesql @contentsql;

View 6 Replies View Related

Stored Procedure Not Inserting Into Linking Table Properly - Two Tables - Two Insert Statements

Dec 9, 2007

Hi can anyone help me with the format of my stored procedure below.
I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true.
At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2
@publicationID Int=null,@typeID smallint=null,
@title nvarchar(MAX)=null,@authorID smallint=null
AS
BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE @ERROR Int
--Create a new publication entry
INSERT INTO Publication (typeID, title)
VALUES (@typeID, @title)
--Obtain the ID of the created publication
SET @publicationID = @@IDENTITY
SET @ERROR = @@ERROR
--Create new entry in linking table PublicationAuthors
INSERT INTO PublicationAuthors (publicationID, authorID)
VALUES (@publicationID, @authorID)
SET @ERROR = @@ERROR
IF (@ERROR<>0)
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

View 9 Replies View Related

Stored Procedure Not Inserting Into Linking Table Properly - Two Tables - Two Insert Statements

Dec 9, 2007

Hi can anyone help me with the format of my stored procedure below.
I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true.
At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2
@publicationID Int=null,@typeID smallint=null,
@title nvarchar(MAX)=null,@authorID smallint=null
AS
BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE @ERROR Int
--Create a new publication entry
INSERT INTO Publication (typeID, title)
VALUES (@typeID, @title)
--Obtain the ID of the created publication
SET @publicationID = @@IDENTITY
SET @ERROR = @@ERROR
--Create new entry in linking table PublicationAuthors
INSERT INTO PublicationAuthors (publicationID, authorID)
VALUES (@publicationID, @authorID)
SET @ERROR = @@ERROR
IF (@ERROR<>0)
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

View 12 Replies View Related

SQL 2012 :: Select Statements And Ended Up Seeing Multiple Cached Instances Of Same Stored Procedure

Nov 24, 2014

I ran the below 2 select statements and ended up seeing multiple cached instances of the same stored procedure. The majority have only one cached instance but more than a handful have multiple cached instances. When there are multiple cached instances of the same sproc, which one will sql server reuse when the sproc is called?

SELECT o.name, o.object_id,
ps.last_execution_time ,
ps.last_elapsed_time * 0.000001 as last_elapsed_timeINSeconds,
ps.min_elapsed_time * 0.000001 as min_elapsed_timeINSeconds,
ps.max_elapsed_time * 0.000001 as max_elapsed_timeINSeconds

[code]...

View 4 Replies View Related

SQL Server Admin 2014 :: Estimated Query Plan For A Stored Procedure With Multiple Query Statements

Oct 30, 2015

When viewing an estimated query plan for a stored procedure with multiple query statements, two things stand out to me and I wanted to get confirmation if I'm correct.

1. Under <ParameterList><ColumnReference... does the xml attribute "ParameterCompiledValue" represent the value used when the query plan was generated?

<ParameterList>
<ColumnReference Column="@Measure" ParameterCompiledValue="'all'" />
</ParameterList>
</QueryPlan>
</StmtSimple>

2. Does each query statement that makes up the execution plan for the stored procedure have it's own execution plan? And meaning the stored procedure is made up of multiple query plans that could have been generated at a different time to another part of that stored procedure?

View 0 Replies View Related

Calling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)

Nov 1, 2007

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 1 Replies View Related

Convert Negative Number Stored As Nvarchar

Feb 4, 2008

I'm working with a horrible database! The field I'm having issues with is a negative number stored as a nvarchar, but it's not stored in a consistent format. I need to convert the field to a decimal (9,2) but I can't because of the negative sign.

Example:

Balance
00000000
0000-413
0000-913
00-10913
00009526

I don't even know where to start to convert this. Any help is appreciated!

Thanks.

View 10 Replies View Related

Full-Text On HTML Stored In Nvarchar(MAX) Column

May 2, 2007

What is the best way of using the Full-Text feature on HTML?
I want to only search the text and omit the html tags.

If that involves storing as a different format, can someone tell me the best way of doing that?
I'm very new to sql and especially full-text.

Thanks.

View 1 Replies View Related

Flush Print Statements During Lengthy Procedure

Apr 3, 2007

I'm working on a stored procedure, and I'm nearing completion. The procedure takes a while to run (it's a very large dataset), but that's not my issue. The problem is that I include several print statements, but I don't get to see any of them until the entire procedure has finished. Is there anything I can do to be able to see them as they are printed? Using sql server 2000 with management studio for 2005.

View 2 Replies View Related

STORED PROCEDURES - IF THEN STATEMENTS

Feb 21, 2008

Hi,

This is driving me up the wall,

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'.

What am i doing wrong?

Thanks

David

View 12 Replies View Related

ERROR: Procedure Expects Parameter '@statement' Of Type 'ntext/nchar/nvarchar'.

Mar 27, 2004

/* INFO USED HERE WAS TAKEN FROM http://support.microsoft.com/default.aspx?scid=kb;en-us;262499 */
DECLARE @X VARCHAR(10)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @Num_Members SMALLINT
SELECT @X = 'x.dbo.v_NumberofMembers'
DECLARE @SQLString AS VARCHAR(500)

SET @SQLString = 'SELECT @Num_MembersOUT=Num_Members FROM @DB'
SET @ParmDefinition = '@Num_MembersOUT SMALLINT OUTPUT'


EXECUTE sp_executesql <-LINE 11
@SQLString,
@ParmDefinition,
@DB = @X,
@Num_MembersOUT = @Num_Members OUTPUT


Just Need Help On This Error
Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 11
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.


I dont know why im getting a errrror b/c I followed http://support.microsoft.com/default.aspx?scid=kb;en-us;262499 exactly

View 3 Replies View Related

DML Statements In Code Vs. Stored Procedures

Aug 4, 2005

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

View 1 Replies View Related

Embedded Sql Statements Vs Stored Procedures

Mar 29, 1999

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.

View 4 Replies View Related

Conditional Statements In Stored Procedures.

Dec 13, 2005

i would like some conditional settings in a stored procedure

i have a variable

@Variable

and I want to do a conditional statement like

if @Variable = 1 then @Variable2 = 1
Elseif @Variable = 3 then @Variable2 = 4
Else @Variable = 11 then @Variable2 = 12

not sure about how to implement elseif bit
i know you can have
{if this Else That} in T-Sql

View 3 Replies View Related

Help With Stored Procedures Using IN / GROUP BY Statements

Jul 20, 2005

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.

View 3 Replies View Related

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?

Thanks in advance,

jessica

View 1 Replies View Related

T-SQL (SS2K8) :: One Stored Procedure Return Data (select Statement) Into Another Stored Procedure

Nov 14, 2014

I am new to work on Sql server,

I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.

Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.

View 1 Replies View Related







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