different parameter to a stored procedure show same results. [Answered]RSS

• • Reply
  G-Oker

  Member

  5 Points

  50 Posts

  different parameter to a stored procedure show same results.

  Nov 11, 2017 01:13 PM|G-Oker|LINK

  I have a stored procedure that takes to variable @on_date and @bitmap. In MSSMS, the query line for the @bitmap looks like

  @bitmap = N'010000'

  and @bitmap is set to char(6),Input,No Default. It runs as it should.

  When I try and send the same value from my controller I get different results.

  The procedure returns different information depending on this bitmap value.

          var outputmodel1 = new List<SP_RESULTS>();
          var command = db.Database.Connection.CreateCommand();
          command.CommandText = "dbo.pr_report";
          command.Parameters.Add(new SqlParameter { ParameterName = "@on_date", SqlDbType = System.Data.SqlDbType.DateTime, Value = ViewBag.usDate });
          command.Parameters.Add(new SqlParameter { ParameterName = "@bitmap", SqlDbType = System.Data.SqlDbType.Char, Value = 10000 });
          command.CommandType = System.Data.CommandType.StoredProcedure;
          command.CommandTimeout = 2400;

  This one results in a huge amount of data being returned.

          var outputmodel2 = new List<SP_RESULTS>();
          var command = db.Database.Connection.CreateCommand();
          command.CommandText = "dbo.pr_report";
          command.Parameters.Add(new SqlParameter { ParameterName = "@on_date", SqlDbType = System.Data.SqlDbType.DateTime, Value = ViewBag.usDate });
          command.Parameters.Add(new SqlParameter { ParameterName = "@bitmap", SqlDbType = System.Data.SqlDbType.Char, Value = 010000 });
          command.CommandType = System.Data.CommandType.StoredProcedure;
          command.CommandTimeout = 2400;

  this should return one line, but instead, returns the same data as the code above

  <div class="comment-body">I've changed the bitmap code to some of the others that it can be ( 000001, 000010 etc), and I get the same results back each time. it's like the parameter is not being passed. if I comment out the line, then the controller fails as bitmap is not defined. </div>

  <div class="comment-body">if i remove the ' ' from the sql query, then i get loads of data back (ie @bitmap = 010000, instead of @bitmap = '010000'. if it put ' ' round my value in the controller, I get told "too many characters ina character literal'</div>

  Could someone point out what I have done wrong ?, as I cannot for the life of me figure it out.

  thanks

• • Reply
  DA924

  Contributor

  3981 Points

  3361 Posts

  Re: different parameter to a stored procedure show same results.

  Nov 11, 2017 04:31 PM|DA924|LINK

  This not a MVC issue.

  You need to debug your sproc by using MS SQL Server Management Studio to run it and see the returned results.

  If you find the post has answered your issue, then please mark post as 'answered'.
• • Reply
  G-Oker

  Member

  5 Points

  50 Posts

  Re: different parameter to a stored procedure show same results.

  Nov 11, 2017 04:36 PM|G-Oker|LINK

  Hi,

  I'm not sure it is an sql probem,as I can see the results in MSSMS, and they are fine, it's the "command.Parameters.Add(new SqlParameter { ParameterName = "@bitmap", SqlDbType = System.Data.SqlDbType.Int, Value = 010000 }); " in my controller that dosnlt seem to be working correctly.

  the SQL query is  EXEC @on_date = N '2016/08/12 00:00:00', @bitmap = N'010000'    

  i think its the  ' ' that io need to pass though , but am unable to                 

• • Reply
  DA924

  Contributor

  3981 Points

  3361 Posts

  Re: different parameter to a stored procedure show same results.

  Nov 11, 2017 06:37 PM|DA924|LINK

  So why don't you just make it string "'01000'" and deal with it in the sproc?

  If you find the post has answered your issue, then please mark post as 'answered'.
• • Reply
  G-Oker

  Member

  5 Points

  50 Posts

  Re: different parameter to a stored procedure show same results.

  Nov 11, 2017 06:42 PM|G-Oker|LINK

  I am unable to change the sproc, as this will affect other programs. it's a great idea though, just not in this case.

• • Reply
  DA924

  Contributor

  3981 Points

  3361 Posts

  Re: different parameter to a stored procedure show same results.

  Nov 11, 2017 07:20 PM|DA924|LINK

  You can also post to the SQL Server forum, General or T-SQL.

  https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?category=sqlserver

  If you find the post has answered your issue, then please mark post as 'answered'.
• • Reply
  mgebhard

  All-Star

  43771 Points

  18743 Posts

  Re: different parameter to a stored procedure show same results.

  Nov 11, 2017 08:42 PM|mgebhard|LINK

  The input is defined as a char(6) but you are passing an integer.  A char(6) is a string 6 bytes long.

  command.Parameters.Add(new SqlParameter { ParameterName = "@bitmap", SqlDbType = System.Data.SqlDbType.Char, Value = "010000" });

  Keep in mind that N'010000' means unicode and the associated type is nchar(6) not char(6).  A unicode character can be one or more bytes.  It's possible that N'010000' is being truncated due to the char(6) type. 

• • Reply
  G-Oker

  Member

  5 Points

  50 Posts

  Re: different parameter to a stored procedure show same results.

  Nov 12, 2017 05:46 PM|G-Oker|LINK

  EXCELLENT. Sooo simple. Thank you