Hello I hope you can help me out on this one?!

I currently have a simple search facility on my webpage using SQL in UltraDev4

SELECT ArtistID, ArtistName, AlbumID, Album, AlbumDate, LabelID, Label, Searchfield
FROM getAlbumSearch
WHERE Searchfield LIKE '%txtSearchField%'
ORDER BY ArtistName asc

Variable
(Name: txtSearchField
Default Value: xyz
Run-time Value: Request("Searchfield"

I have a text box named txtSearchfield that the user can enter their search criteria into. I have set up a query 'getSearchAlbum' that combines Album Title and Artist Name together for the user to search in.

I have tried to convert this into a stored procedure using MSSQL 2000 but it is not working! Where am I going wrong? :
CREATE PROCEDURE spSearchAlbums
(@SearchField varchar)
AS
SELECT AlbumID, ArtistID, ArtistName, Album, AlbumDate, LabelID, Label, Searchfield
FROM dbo.getAlbumSearch
WHERE Searchfield LIKE @SearchField

Any advice will be of great help
Best
Chapperz

Hi Chapperz,

try putting the length of the varchar variable in the stored procedure, otherwise I believe you only get the first character.

CREATE PROCEDURE spSearchAlbums
@SearchField varchar(100)
AS
SELECT AlbumID, ArtistID, ArtistName, Album, AlbumDate, LabelID, Label, Searchfield
FROM dbo.getAlbumSearch
WHERE Searchfield LIKE @SearchField

Digga

Sharing Knowledge Saves Valuable Time!!!

Edited by - Digga the Wolf on 19 Jan 2003 22:42:54