sql stored procedure ( code error )

hello guys.....I am new to writing stored procedures and pretty weak in sql , but I am writing this sql sp and it gives me error ( near as , near if )

please if someone can make it correct for me I'd be really helpful , what am trying to do is 3 types of searching , by uploadername , by id , by name ...search by id is a bit complicated , that when someone enters the id , it'll show the file related to that id , plus all files who has higher id number , and has the same uploader

CREATE PROCEDURE display_results

@Name varchar(120),

@Uploader varchar(50),

@ID int,

AS

IF ( @ID != NULL )

DECLARE @username as varchar(50)

SET @username = ( Select Uploader from Files where ID = @ID )

END IF

SELECT
[ID], [Name], [Type], [Size], [Uploader], [fullName] FROM [Files] WHERE ( @Name IS NULL OR Name LIKE '%' + @Name + '%' ) AND ( @Id IS NULL OR ID >= @ID and Uploader = @username ) AND ( @Uploader IS NULL OR Uploader LIKE '%' + @Uploader + '%' )

RETURN

 

 

 

0
Mazenx
12/9/2008 10:10:18 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

12 Replies
525 Views

Similar Articles

[PageSpeed] 19

  • Remove the "," after @ID Int
  • use END not END IF.
  • when you check for NULLs you need to use IF @ID IS NOT NULL and not IF ( @ID != NULL )

***********************
Dinakar Nethi
Life is short. Enjoy it.
***********************
0
ndinakar
12/9/2008 10:42:28 PM
It says incorrect syntax near keyword select , wonder which select of them.
0
Mazenx
12/9/2008 11:23:07 PM

Try

CREATE PROCEDURE display_results

@Name varchar(120),

@Uploader varchar(50), @ID int

AS

IF @ID IS NOT NULL

BEGIN

DECLARE @username as varchar(50)

SET @username = ( Select Uploader from Files where ID = @ID )

END

SELECT [ID], [Name], [Type], [Size], [Uploader], [fullName]

FROM [Files] WHERE ( @Name IS NULL OR [Name] LIKE '%' + @Name + '%' )

AND ( @Id IS NULL OR ID >= @ID and Uploader = @username )

AND ( @Uploader IS NULL OR Uploader LIKE '%' + @Uploader + '%' )

RETURN

 

Also which SQL vesrion you're running?


Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
0
Naom
12/10/2008 3:34:42 AM

 Hi,Mazenx

Ya I agree with above

Mazenx:
@ID int,
 

Remove comma from last 

 

 

 

Mazenx:

IF ( @ID != NULL )

DECLARE @username as varchar(50)

SET @username = ( Select Uploader from Files where ID = @ID )

END IF

 

 

Above

Remember While using  if statement

try to make habit of writing begin and end 

for the statements linked inside if

ie 

 

IF ( @ID != NULL )

begin

DECLARE @username as varchar(50)

SET @username = ( Select Uploader from Files where ID = @ID )

end


 

 

 

PLZ MARK AS ANSWER IF IT HELP U.

THANKS.


 

 


RAGHAV

MVP ASP/ASP.Net Read My Blog


MARK THE POST AS ANSWER IF IT HELPS U.


"Success doesn't come to you…you go to it."--Marva Collins




"Success does not come to those who wait . . . and it does not wait for anyone to come to it." Anonymous


0
raghav_khunger
12/10/2008 5:05:59 AM

Thanks noam....see I learned a bit but wrote a mess and now the mess went working ! lol...thanks alot it works so well !

0
Mazenx
12/10/2008 1:04:43 PM

No problem, see my sig :)


Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
0
Naom
12/10/2008 2:40:18 PM

hey noam....just a little last thing , the sp i wrote returns all records if someone wanted to become silly and just write files.aspx without any query string...how can i fix that ?!?

here is it

ALTER PROCEDURE display_results

@Name

varchar(120),@Uploader varchar(50), @ID int

AS

IF

@ID IS NOT NULL

BEGIN

DECLARE @username as varchar(50)

SET @username = ( Select Uploader from Files where ID = @ID )

END

SELECT

[ID], [Name], [Type], [Size], [Uploader], [fullName]

FROM

[Files] WHERE ( @Name IS NULL OR [Name] LIKE '%' + @Name + '%' )

AND

( @Id IS NULL OR ID >= @ID and Uploader = @username )

AND

( @Uploader IS NULL OR Uploader LIKE '%' + @Uploader + '%' )

RETURN

0
Mazenx
12/12/2008 4:37:19 PM

 Hi,

   In your aspx page you can have a condition like:

if (Request.QueryString[name] != null)

{

   // Here only you can call your procedure
 

}

else

{

   // You can write a message saying no parameters or some meaning full message.
 

}
 


-Sri
-------------------------------------------------
If this post was useful to you, please mark it as answer. Thank you!
0
ksridharbabuus
12/12/2008 4:45:53 PM

hmmm....but I am making the procedure call inside the sqldatasource configuration....and the gridview is tied to that sqldatasource

0
Mazenx
12/12/2008 4:58:04 PM

 Hi,

   In that case you can pass an empty string/null/default value to the stored procedure. You can handle in procedure depending on the parameter value (especially when it is null/empty string).
 


-Sri
-------------------------------------------------
If this post was useful to you, please mark it as answer. Thank you!
0
ksridharbabuus
12/12/2008 5:04:27 PM

hmmm..am always passing a string to the procedure , but i found this error when someone write my website name and files.aspx with passing nothing , the gridview then shows all records.

0
Mazenx
12/12/2008 11:35:37 PM

Hi,

  That is because in your stored procedure you are using % prefix for the parameter in the where clause.

When you are not passing the value, it will consider the % and will return all the values.

You can add an additional if condition in the SP and check for the Null or empty, if not execute the query with the parameter.
 


-Sri
-------------------------------------------------
If this post was useful to you, please mark it as answer. Thank you!
0
ksridharbabuus
12/13/2008 12:44:31 PM
Reply:

Similar Artilces:

Dynamic SQL Error SQL error code = -104 Token unknown in procedure
Hi all, I'm a newbie. I test my procedure in SQL interactive but got error: -------------------------------------------------------- Error at line 1 Dynamic SQL Error SQL error code = -104 Token unknown - line 5, char 3 : SQL - CREATE PROCEDURE CreateNewSystem(NewSystemID VarChar(10), CurrentSelection Int, NewSystemName VarChar(20), CheckChild Boolean) AS DECLARE VARIABLE LeftRightValue INTEGER; begin IF :CheckChild THEN SELECT LeftRightValue = lft FROM "MEP_SYSTEMS" WHERE category_id=:CurrentSelection; ELSE SELECT LeftRightValue = rgt FROM "MEP_...

Dynamic SQL Error SQL error code = -104 Token unknown in procedure #2
Hi all, I'm a newbie. I test my procedure in SQL interactive but got error: -------------------------------------------------------- Error at line 1 Dynamic SQL Error SQL error code = -104 Token unknown - line 5, char 3 : SQL - CREATE PROCEDURE CreateNewSystem(NewSystemID VarChar(10), CurrentSelection Int, NewSystemName VarChar(20), CheckChild Boolean) AS DECLARE VARIABLE LeftRightValue INTEGER; begin IF :CheckChild THEN SELECT LeftRightValue = lft FROM "MEP_SYSTEMS" WHERE category_id=:CurrentSelection; ELSE SELECT LeftRightValue = rgt FROM "MEP_...

how to insert sql stored procedure into vb.net code??
I am trying to create a paged web site from my database file. for that i found an appropriate sql code. the only thing i don't know is how to declare this sql inside the vb. is it supposed to be in the same .aspx file or in another one?? pls help if you know. thank you so much. here's the sql stored procedure: ------------------------------------------------------------ CREATE PROCEDURE sp_PagedItems ( @Page int, @RecsPerPage int ) AS -- We don't want to return the # of rows inserted -- into our temporary table, so turn NOCOUNT ON SET NOCOUNT ON --Cr...

Error Sorting a GridView with a SQL Stored Procedure DataSource
I have a page with a gridview that gets data from a sql stored procedure.  The gridview renders the data correctly on page load.  When a column header is clicked, the sorting event fires, gets the sort expression and sort order, passes that to the populategridview() sub, and the stored procedure re-runs - sorting in the requested order.  However when the data binds to the gridview after sorting, the sorting event is fired AGAIN, caused the sort order to be wrong.  See sample code:Private Sub PopulateViewTestsGridView(Optional ByVal SortExpression As String = "Su...

ADO.NET 2.0 - Three-tier application - connection string error
Using ASP.NET 2.0 beta, VS2005 beta 1, W2k3 server with IIS I try to build a three-tier app structure as followed: - the store procedure in SQL with a input parameter as Username and retrieve the UserId as output parameter. - the business logic (class) with connection string defined in web.config (“AppConnectionStringASPNETapp”) - the presentation aspx file after the user has logged in through login.aspx I have work out 2 scenarios with 2 different errors. Scenario 1: Here is the code is business logic (class) (catalog.cs) using System; using System.Data; usi...

Copy stored procedures from SQL to SQL DE?
Is there an easy way to copy all stored procedures from an SQL server to en SQL Desktop Engine or do I have build all stored procedures by hand?Johan TheunissenMCPD, MCSE, MCTS BizTalk 2006==============================Please mark the most helpful reply/replies as "Answer". You can use the SQL Server import/export wizard, or you can generate a script of them using SQL Server Management Studio. Thanx, In the mean time I copied them manually (copy-paste). So I think a seperate script would be usefull when you have 100+ stored procedures. Or have the script available.Johan Theu...

Linq to SQL and stored procedure with dynamic sql
I have a stored procedure like create proc test as ..... set @sql = 'select ' + .... + ' from ....' exec(@sql)The select list is dynamically (in fact, pivot table). However, after drag the stored procedure to dbml design view and called, the stored procedure only return an integer.How to get the result set of the stored procedure when using dbml?Thanks,  Is this the only SELECT statement that @sql gets?  I created a simple example and it performed as I would expect returning a complete recordset....I'm wondering if your stored proc is returning 2 record se...

VB.NET SQL stored procedure: procedure has no parameters and arguments were supplied
VB.NET SQL stored procedure: procedure has no parameters and arguments were supplied Please assist me:This erorr message is produce when calling the stored procedure in vb.netProcedure AutomateMatterNumber has no parameters and arguments were supplied."MS SQL 2000stored procedure:*/CREATE PROCEDURE dbo.AutomateMatterNumber ASDECLARE @nextMtr AS BIGINTDECLARE @dtToday AS DATETIMEIF NOT EXISTS(SELECT * FROM tempMatter WHERE DATEDIFF(dd,DateSet,GETDATE())=0 )BEGINDELETE FROM tempMatter-- incase there are some old recordsSELECT TOP 1 @nextMtr= CONVERT(BIGINT, MatterNumber) + 1 ,@dtTod...

Error in sql stored procedure
I've a stored procedure which returns values based on 7 criterias. It was working fine and returned the values properly. I added one more criteria for returning values from 2 database columns based on minimum and maximum values. It's not working properly and gives syntax error. Could someone tell me what mistake I'm doing? Thanks. ALTER procedure [dbo].[USP_Account_Search_Mod] @ClientCode VARCHAR(7) = '' ,@DebtorName VARCHAR(25) = '',@DebtorNumber INT = 0 ,@AccountNumber VARCHAR(30) = '' ,@ReferenceNumber VARCHAR(30) = '',@Tier INT = 0 ...

Error SQL Stored Procedure
Yo people, got a little problem with this stored procedure, i go to save it and it kicks out these errors: Incorrect syntax near the keyword 'Drop'.Incorrect syntax near 'Go'.Incorrect syntax near 'Go'.'CREATE/ALTER PROCEDURE' must be the first statement in the query batch I dont no about this sort of stuff so a good break down of what wrong would be good, below is the whole procedure. CREATE PROCEDURE dbo.SQLDataSource1 Drop Table PersonGo  Create Table Person (PersonID Int Identity, PersonEmail Varchar(255),PersonName Varchar(255), PersonSe...

SQL Stored Procedure Issue
This is the Stored Procedure below ->  SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GO /****** Object:  Stored Procedure dbo.BPI_SearchArchivedBatches    Script Date: 5/18/2007 11:28:41 AM ******/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BPI_SearchArchivedBatches]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[BPI_SearchArchivedBatches]GO /****** Object:  Stored Procedure dbo.BPI_SearchArchivedBatches    Script Date: 4/3/2007 4:50:23 PM ******/ /****** Object:  Stored Proc...

Sql Query String verses SQL Stored Procedure
I'm working on the performace of my pages. I want to allow large datasets and get the best performance I can. I've read that Stored Proceedures(SP) are faster than passing a Query String(QS). The questions I have are: Given the code of this page, will I see an improvement in performance using a SP? If yes, how do I allow for just SOME of the variables being passed? Users are nor required to enter all parameters. As far as I know, if you create a SP in SQL Server and don't pass ALL of the parameters it expects, you'll get an error. Also, someone mentioned that string concatinati...

SQL Stored Procedure to MySQL Stored Procedure Conversion
Hi, I am trying to convert a stored procedure written for sql to one that will work in mysql. I understand that I have to set the variables as IN , but I don't know what to do with the rest of the code. The following is the sql stored procedure that I am trying to convert to msql stored procedure: CREATE PROCEDURE Register_User (@userName Varchar(50), (@PassWord Varchar(50), (@FirstName Varchar(50), (@LastName Varchar(50) ) AS IF EXISTS(SELECT u_ID FROM User_ID Where u_UserName=@UserName) RETURN -1 ELSE INSERT User_ID( u_User...

LINQ in a SQL CLR Stored Procedure (SQL 2008)
Hey all, i'd like to use LINQ in a CLR Stored Procedure in SQL 2008. The problem is, i can't add a reference to System.Xml.Linq. How do i make that happen? my SQL project is targeting .net 3.5, and i have SP1 installed.I saw a blog about this somewhere recently but now i can't find it. so if you can point me to something, that would be awesome.thanks,-b I googled and found this blog http://oakleafblog.blogspot.com/2007/09/problems-using-stored-procedures-for_08.html  Beware of bugs in the above code; I have only proved it correct, not tried it. (Donald Knuth) That does...

Web resources about - sql stored procedure ( code error ) - asp.net.sql-datasource

Invasiveness of surgical procedures - Wikipedia, the free encyclopedia
There are three main categories which describe the invasiveness of surgical procedures . These are: non-invasive procedures , minimally invasive ...

Procedure is more a snap than a snip
A QUEENSLAND doctor is bidding to set an unusual world record by performing the highest number of vasectomies in one day, with the help of fellow ...

Rushed cosmetic procedures a 'recipe for disaster'
​When Chanelle O'Hare went searching online for a deal on potential cosmetic procedures, she could not have imagined that what she ended ...

Jetstar procedures under investigation after planes took off too heavy
Jetstar's procedures for calculating the weight of its aircraft are under review by the Australian Transport Safety Bureau after two of its planes ...

Old Lady Lawyer: Uncivil Procedure
What is some of the worst behavior you've witnesses by attorneys?

NFL Announces Changes to Officiating Procedures for Playoffs - Bleacher Report
The NFL formally approved changes to its postseason officiating procedures to allow referees the opportunity to consult Vice President of Officiating ...

Will Paul Ryan Make His Mark As Speaker By Instituting Impeachment Procedures Against President Obama ...
Wisconsin Ayn Rand devotee Paul Ryan just started his new job as Speaker of the dysfunctional House Republicans. And he's already headed for ...

TSA Updates Screening Procedure, Will Mandate Some Passengers Use Full-Body Scanners
... Imaging Technologies, or AIT, in favor of full-body pat-downs by TSA agents. Under the new mandate, not everyone can opt for the pat-down procedure. ...

'Painless' dental cavity procedure regrows tooth enamel
... "cavity" a lot of people sweat thinking about painful injections and relentless drilling. But scientists in Britain have developed a new procedure ...

'Daily Show' Host Trevor Noah Hospitalized for Emergency Procedure
'Daily Show' Host Trevor Noah Hospitalized for Emergency Procedure

Resources last updated: 12/24/2015 10:04:22 AM