[T-SQL] Pass semicolon deliminated parameter to stored procedure |
| Pass semicolon deliminated parameter to stored procedure Posted: 15 Sep 2013 11:44 PM PDT Hell All,I am facing a bit of challenge:I have a table with the following structure: [code="sql"] [tabid] [int] IDENTITY(1,1) NOT NULL, [tabname] [nvarchar](50) NULL, [description] [nvarchar](50) NULL, [url] [nvarchar](50) NULL, [parent] [int] NULL, [roles] [nvarchar](50) NULL, [tabposition] [int] NULL, [tabstatus] [int] NULL,[/code]Some sample data is the following:1 Home Dashboard en.aspx/portalmodules/dashboard NULL 1;6; 1 12 Maintenance Maintenance en.aspx/maintenance/maintenance NULL 1; 7 1126 Site Site enbank.aspx/site NULL 11; 2 1I wrote a stored procedure that takes a string of values, seperated by semicolon as parameter. The procedure is below;[code="sql"]ALTER PROCEDURE [dbo].[selectUserTabsByRoles] @var varchar(max)ASBEGIN SELECT distinct * from tbl_tabs where ( PATINDEX('%'+left(@var,1)+'%', roles) > 0 or PATINDEX('%'+right(@var,1)+'%', roles) > 0 ) AND parent is null and tabstatus =1 ORDER BY tabposition END[/code]My problem is, when I pass a parameter like 1; it fetches all rows with roles having 1. But I realised that the last row in the sample data does not have 1 as roles, but rather 11.I urgently need help on this.Thanks |
| Posted: 15 Sep 2013 06:16 PM PDT HI all,here is the scenario that I faced:1. I had a table which some data.2. I need to add a column with not null, While I was doing in this manner. [code="sql"]Alter table table1ADD column1 int not null[/code] I got an error that Alter table statement not allowed for not null3. then, I truncate the table and execute same statement as above and it executed successfully4. after this, I was again inserting the data in the table in this manner[code="sql"]insert into table1......Select column names from table_name[/code]While doing this, I again got the error that Insertion failed as table1 column column1 dont allow NOT NULL values.When I checked in the table I found that there is no null values in statement[code="sql"]Select column names from table_name [/code]5. then I drop and recreate the table again and this script run fine and data gets inserted.But I dont understand the behaviour why does it happen?Can anyone please explain me? |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No comments:
Post a Comment